Created
May 23, 2012 14:33
-
-
Save jrusbatch/2775538 to your computer and use it in GitHub Desktop.
Find all columns in all tables that contain only null or empty strings
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
declare @tempTable table | |
( | |
TableSchema nvarchar(256), | |
TableName nvarchar(256), | |
ColumnName sysname, | |
NotNullCnt bigint | |
); | |
declare @sql nvarchar(4000); | |
declare @tableSchema nvarchar(256); | |
declare @tableName nvarchar(256); | |
declare @columnName sysname; | |
declare @cnt bigint; | |
declare columnCursor cursor for | |
select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS | |
where IS_NULLABLE = 'YES'; | |
open columnCursor; | |
fetch next from columnCursor into @tableSchema, @tableName, @columnName; | |
while @@FETCH_STATUS = 0 | |
begin | |
-- use dynamic sql to get count of records where column is not null | |
set @sql = 'select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName + | |
'] where [' + @columnName + '] is not null and len(ltrim(rtrim([' + @columnName + ']))) > 0' | |
print @sql | |
exec sp_executesql @sql, N'@cnt bigint output', @cnt = @cnt output | |
print @cnt | |
insert into @tempTable select @tableSchema, @tableName, @columnName, @cnt; | |
fetch next from columnCursor into @tableSchema, @tableName, @columnName; | |
end; | |
close columnCursor; | |
deallocate columnCursor; | |
select * from @tempTable where NotNullCnt = 0 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment