這是sql 2005或更高版本：用表名替換ADDR_Address。

declare @col varchar(255), @cmd varchar(max)

DECLARE getinfo cursor for

SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID

WHERE t.Name = 'ADDR_Address'

OPEN getinfo

FETCH NEXT FROM getinfo into @col

WHILE @@FETCH_STATUS = 0

BEGIN

    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM ADDR_Address WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'

    EXEC(@cmd)

    FETCH NEXT FROM getinfo into @col

END

CLOSE getinfo

DEALLOCATE getinfo

這應(yīng)該為您提供表“ Person”中所有列只有NULL值的列表。您將獲得多個(gè)結(jié)果集的結(jié)果，這些結(jié)果集為空或包含單個(gè)列的名稱。您需要在兩個(gè)地方替換“ Person”，才能將其與另一個(gè)表一起使用。

DECLARE crs CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM syscolumns WHERE id=OBJECT_ID('Person')

OPEN crs

DECLARE @name sysname

FETCH NEXT FROM crs INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

    EXEC('SELECT ''' + @name + ''' WHERE NOT EXISTS (SELECT * FROM Person WHERE ' + @name + ' IS NOT NULL)')

    FETCH NEXT FROM crs INTO @name

END

CLOSE crs

DEALLOCATE crs

這是Bryan在2008年及以后的查詢的更新版本。它使用INFORMATION_SCHEMA.COLUMNS，為表架構(gòu)和表名稱添加變量。列數(shù)據(jù)類型已添加到輸出中。查找特定數(shù)據(jù)類型的列時(shí)，包含列數(shù)據(jù)類型會(huì)有所幫助。我沒有添加列寬或其他任何內(nèi)容。

對(duì)于輸出，使用RAISERROR ... WITH NOWAIT，因此文本將立即顯示，而不是像PRINT那樣一次全部顯示（大部分）。

SET NOCOUNT ON;

DECLARE

 @ColumnName sysname

,@DataType nvarchar(128)

,@cmd nvarchar(max)

,@TableSchema nvarchar(128) = 'dbo'

,@TableName sysname = 'TableName';

DECLARE getinfo CURSOR FOR

SELECT

     c.COLUMN_NAME

    ,c.DATA_TYPE

FROM

    INFORMATION_SCHEMA.COLUMNS AS c

WHERE

    c.TABLE_SCHEMA = @TableSchema

    AND c.TABLE_NAME = @TableName;

OPEN getinfo;

FETCH NEXT FROM getinfo INTO @ColumnName, @DataType;

WHILE @@FETCH_STATUS = 0

BEGIN

    SET @cmd = N'IF NOT EXISTS (SELECT * FROM ' + @TableSchema + N'.' + @TableName + N' WHERE [' + @ColumnName + N'] IS NOT NULL) RAISERROR(''' + @ColumnName + N' (' + @DataType + N')'', 0, 0) WITH NOWAIT;';

    EXECUTE (@cmd);

    FETCH NEXT FROM getinfo INTO @ColumnName, @DataType;

END;

CLOSE getinfo;

DEALLOCATE getinfo;