USE [Database name]
DECLARE @colName VARCHAR(200)
DECLARE @tabName VARCHAR(200)
DECLARE @nullTable TABLE
(TABLE_NAME varchar(200),
COLUMN_NAME varchar(200),
NOTNULLCOUNT int)
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT T.NAME , C.NAME
FROM SYS.OBJECTS AS T
JOIN SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC='USER_TABLE';
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @tabName,@colName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Count AS INT
DECLARE @SQLString nvarchar(500)
SET @SQLString =N'SELECT '''+@tabName+''','''+@colName+''',count(*) FROM dbo.['+@tabName+'] WHERE ['+@colName+'] IS NOT NULL'
PRINT @SQLString
INSERT @nullTable EXEC (@SQLString)
FETCH NEXT FROM @MyCursor
INTO @tabName,@colName
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
SELECT * FROM @nullTable WHERE NOTNULLCOUNT=0 ORDER BY TABLE_NAME,COLUMN_NAME
DECLARE @colName VARCHAR(200)
DECLARE @tabName VARCHAR(200)
DECLARE @nullTable TABLE
(TABLE_NAME varchar(200),
COLUMN_NAME varchar(200),
NOTNULLCOUNT int)
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT T.NAME , C.NAME
FROM SYS.OBJECTS AS T
JOIN SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC='USER_TABLE';
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @tabName,@colName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Count AS INT
DECLARE @SQLString nvarchar(500)
SET @SQLString =N'SELECT '''+@tabName+''','''+@colName+''',count(*) FROM dbo.['+@tabName+'] WHERE ['+@colName+'] IS NOT NULL'
PRINT @SQLString
INSERT @nullTable EXEC (@SQLString)
FETCH NEXT FROM @MyCursor
INTO @tabName,@colName
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
SELECT * FROM @nullTable WHERE NOTNULLCOUNT=0 ORDER BY TABLE_NAME,COLUMN_NAME
No comments:
Post a Comment