EPkdyKbx

1declare @col varchar(255), @cmd varchar(max)
2
3DECLARE getinfo cursor for
4SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
5WHERE t.Name = 'ADDR_Address'
6
7OPEN getinfo
8
9FETCH NEXT FROM getinfo into @col
10
11WHILE @@FETCH_STATUS = 0
12BEGIN
13    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM ADDR_Address WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'
14    EXEC(@cmd)
15
16    FETCH NEXT FROM getinfo into @col
17END
18
19CLOSE getinfo
20DEALLOCATE getinfo
21	
22DECLARE crs CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM syscolumns WHERE id=OBJECT_ID('Person')
23OPEN crs
24DECLARE @name sysname
25FETCH NEXT FROM crs INTO @name
26WHILE @@FETCH_STATUS = 0
27BEGIN
28    EXEC('SELECT ''' + @name + ''' WHERE NOT EXISTS (SELECT * FROM Person WHERE ' + @name + ' IS NOT NULL)')
29    FETCH NEXT FROM crs INTO @name
30END
31CLOSE crs
32DEALLOCATE crs
33	
34select 
35  count(<columnName>)
36from
37  <tableName>
38	
39select 
40    case(count(<columnName>)) when 0 then 'Nulls Only' else 'Some Values' end
41from 
42    <tableName>
43	
44SELECT COUNT(DISTINCT field) FROM tableName
45	
46SET NOCOUNT ON
47DECLARE @TableName Varchar(100)
48SET @TableName='YourTableName'
49CREATE TABLE #NullColumns (ColumnName Varchar(100), OnlyNulls BIT)
50INSERT INTO #NullColumns (ColumnName, OnlyNulls) SELECT c.name, 0 FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.id AND o.name = @TableName AND o.xtype = 'U'
51DECLARE @DynamicSQL AS Nvarchar(2000)
52DECLARE @ColumnName Varchar(100)
53DECLARE @RC INT
54    SELECT TOP 1 @ColumnName = ColumnName FROM #NullColumns WHERE OnlyNulls=0
55    WHILE @@ROWCOUNT > 0
56    BEGIN
57        SET @RC=0
58        SET @DynamicSQL = 'SELECT TOP 1 1 As HasNonNulls FROM ' + @TableName + ' (nolock) WHERE ''' + @ColumnName + ''' IS NOT NULL'
59        EXEC sp_executesql @DynamicSQL
60        set @RC=@@rowcount
61        IF @RC=1
62        BEGIN
63            SET @DynamicSQL = 'UPDATE #NullColumns SET OnlyNulls=1 WHERE ColumnName=''' + @ColumnName + ''''
64            EXEC sp_executesql @DynamicSQL
65        END
66        ELSE
67        BEGIN
68            SET @DynamicSQL = 'DELETE FROM #NullColumns WHERE ColumnName=''' + @ColumnName+ ''''
69            EXEC sp_executesql @DynamicSQL
70        END
71    SELECT TOP 1 @ColumnName = ColumnName FROM #NullColumns WHERE OnlyNulls=0
72    END
73
74SELECT * FROM #NullColumns
75
76DROP TABLE #NullColumns
77SET NOCOUNT OFF
78	
79for each col
80begin
81  @cmd = 'if not exists (select * from tablename where ' + col + ' is not null begin print ' + col + ' end'
82exec(@cmd)
83end
84	
85foreach $column ($cols) {
86   query("SELECT count(*) FROM table WHERE $column IS NOT NULL")
87   if($result is zero)  {
88      # $column contains only null values"
89      push @onlyNullColumns, $column;
90   } else {
91      # $column contains non-null values
92   }
93}
94return @onlyNullColumns;