SQL Server Tips

From the Database Community Forum

May 2004

How to insert NULL values into a SQL Server table using Enterprise Manager

From time to time you might want to change the data in a column from some non-NULL value to NULL. In T-SQL, of course, you just use the NULL keyword in your UPDATE statement, but what if you're using Enterprise Manager? There's a shortcut key to storing a NULL value in a column. Simply position the cursor in the field in question, then press [Ctrl]0 (that's a zero, not an O). Simply selecting the data in the column and pressing [Delete] doesn't work. For character data, you end up with an empty string, and for numeric and date columns, Enterprise Manager doesn't allow the operation at all. So, if you want to use NULLS, remember [Ctrl]0.

Determine which SQL Server tables have associated triggers

If you need to know which tables in a particular database have associated triggers and what types of triggers they are, then take a look at the following query:

SELECT a.name table_name,a.id table_id,
	b.id trigger_id ,b.name trig_name,
	a.deltrig  ,a.updtrig ,a.instrig
FROM sysobjects a, sysobjects b
WHERE a.type='U' AND (a.id = b.deltrig OR a.id = 
	b.updtrig OR a.id = b.instrig)
	AND a.name IN (SELECT name FROM sysobjects 
	WHERE type = 'U')
ORDER BY 1

Group and count records based on the data in a single column in SQL Server

SQL Server has the ability to group rows by the values in a particular column and return the number of instances of each value. To do so, use this syntax:

SELECT CustomerID, COUNT(*) Occurences
	FROM Orders
	GROUP BY CustomerID

In addition, if you want to find only those values that occur one time, you can add a HAVING COUNT(*) = 1 clause to the end of the query. For that matter, you can put in any number you like here. You could find, for example, only those values that occur exactly twice, between 10 and 20 times, or more than 50 times.

Find the number of rows for every table in your SQL Server database

If you need to determine quickly and easily how many rows each table in a database contains, you can use the following cursor operation. Note that the performance of this code will vary in proportion to the size of your tables--in other words, the bigger your tables, the slower this cursor will run. But it gets the job done quite effectively nevertheless and is a good example of the use of dynamic T-SQL.

USE DbName -- Substitute your database here
	GO

	SET NOCOUNT ON
	DECLARE TablesCursor CURSOR FOR SELECT name FROM sysobjects WHERE type = 'U'
	OPEN TablesCursor
	DECLARE @TableName varchar(30)

	FETCH NEXT FROM TablesCursor INTO @TableName
	WHILE (@@fetch_status <> -1)
	BEGIN
		EXEC('SELECT COUNT(*) AS ' + @TableName + ' FROM ' +  @TableName)
		FETCH NEXT FROM TablesCursor INTO @TableName
	END
	DEALLOCATE TablesCursor
	SET NOCOUNT OFF

Back to SQL Server Tips | Back to ZDNet/Element K Tips

Alrak's Course Resources ©2002-2007 Karla Carter. All rights reserved. This material (including, but not limited to, Mini-lectures and Challenge Labs) may not be reproduced, displayed, modified or distributed without the express prior written permission of the copyright holder. For permission, contact Karla.