Two Useful SQL Queries

On my current project at Adage Technologies, I had the opportunity to take an existing SQL Server database and to redesign the database structure.  The existing database had no foreign key relationships and contained duplicate data in various tables and columns.  One of the first tasks I went about was attempting to determine which columns were not used or contained the same value for all records.  I googled and discovered a post on Stack Overflow regarding finding database columns with only null values.

http://stackoverflow.com/questions/63291/sql-select-columns-with-null-values-only

This addressed one of the scenarios I was attempting to eliminate, where the column was never used and only contained nulls.  I was also looking for columns that contained the same value repeated in all columns.  I therefore modified Charles Graham’s answer from Stack Overflow, and created the query below:

1: DECLARE @col varchar(255), @table varchar(255), @cmd varchar(max), @cmd2 varchar(max)
2: 
3: DECLARE getTableName CURSOR FOR
4: SELECT [name] FROM sys.tables
5: 
6: OPEN getTableName
7: 
8: FETCH NEXT FROM getTableName INTO @table
9: 
10: WHILE @@FETCH_STATUS = 0
11: BEGIN
12:        DECLARE getinfo CURSOR FOR
13:        SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
14:        WHERE t.Name = @table
15: 
16:        OPEN getinfo
17: 
18:        FETCH NEXT FROM getinfo INTO @col
19: 
20:        WHILE @@FETCH_STATUS = 0
21:        BEGIN
22:                SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM [' + @table + ']
23: WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @table + ' - ' +
24: @col + ' - (null)'' END'
25:                EXEC(@cmd)
26: 
27:                SELECT @cmd2 = 'SET ANSI_Warnings OFF DECLARE @count int, @val
28: varchar(255) SELECT @count = (SELECT COUNT(DISTINCT [' + @col + '])
29: FROM [' + @table + ']) IF (@count < 2) BEGIN SELECT @val = (SELECT TOP
30: 1 [' + @col + '] FROM [' + @table + ']) print ''' + @table + ' - ' +
31: @col + ' - '' + @val + '''' END'
32:                EXEC (@cmd2)
33: 
34:                FETCH NEXT FROM getinfo INTO @col
35:        END
36: 
37:        CLOSE getinfo
38:        DEALLOCATE getinfo
39: 
40:        FETCH NEXT FROM getTableName INTO @table
41: 
42: END
43: 
44: CLOSE getTableName
45: DEALLOCATE getTableName
46: 

The modified query returns all columns that have the same value in every record.  The query prints out results in the following format: Table – Column – Value.

The second query that I found useful while testing the success of my migration code was the following query written by Mitchel Sellers (http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/121/determing-sql-server-table-size.aspx).

This query returns the record count for all tables, as well as information on the amount of space used by each table.

1: CREATE PROCEDURE GetAllTableSizes
2: AS
3: /*
4:     Obtains spaced used data for ALL user tables in the database
5: */
6: DECLARE @TableName VARCHAR(100)    --For storing values in the cursor
7: 
8: --Cursor to get the name of all user tables from the sysobjects listing
9: DECLARE tableCursor CURSOR
10: FOR 
11: select [name]
12: from dbo.sysobjects 
13: where  OBJECTPROPERTY(id, N'IsUserTable') = 1
14: FOR READ ONLY
15: 
16: --A procedure level temp table to store the results
17: CREATE TABLE #TempTable
18: (
19:     tableName varchar(100),
20:     numberofRows varchar(100),
21:     reservedSize varchar(50),
22:     dataSize varchar(50),
23:     indexSize varchar(50),
24:     unusedSize varchar(50)
25: )
26: 
27: --Open the cursor
28: OPEN tableCursor
29: 
30: --Get the first table name from the cursor
31: FETCH NEXT FROM tableCursor INTO @TableName
32: 
33: --Loop until the cursor was not able to fetch
34: WHILE (@@Fetch_Status >= 0)
35: BEGIN
36:     --Dump the results of the sp_spaceused query to the temp table
37:     INSERT  #TempTable
38:         EXEC sp_spaceused @TableName
39: 
40:     --Get the next table name
41:     FETCH NEXT FROM tableCursor INTO @TableName
42: END
43: 
44: --Get rid of the cursor
45: CLOSE tableCursor
46: DEALLOCATE tableCursor
47: 
48: --Select all records so we can use the reults
49: SELECT * 
50: FROM #TempTable
51: 
52: --Final cleanup!
53: DROP TABLE #TempTable
54: 
55: GO

These two queries were both extremely useful during the database design and migration process.