Wednesday, January 27, 2016

Finding out Null valued columns in a database

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

No comments:

Post a Comment