Posted By

ezerick on 03/26/10


Tagged

sql tsql empty all find columns


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

g8rpal
Tyster


TSQL - Find All Empty Columns in a Database


 / Published in: SQL
 

URL: http://blog.hoegaerden.be/2009/02/15/script-find-all-empty-columns-in-database/

I found this and simply want to store it. It was done by Valentino Vranken

  1. -- Returns a list of all columns in current database
  2. -- where the column's value is null for all records.
  3. declare @tempTable TABLE
  4. (
  5. TableSchema nvarchar(256),
  6. TableName nvarchar(256),
  7. ColumnName sysname,
  8. NotNullCnt bigint
  9. );
  10.  
  11. declare @sql nvarchar(4000);
  12. declare @tableSchema nvarchar(256);
  13. declare @tableName nvarchar(256);
  14. declare @columnName sysname;
  15. declare @cnt bigint;
  16.  
  17. declare columnCursor cursor FOR
  18. SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
  19. WHERE IS_NULLABLE = 'YES';
  20.  
  21. open columnCursor;
  22.  
  23. fetch next FROM columnCursor INTO @tableSchema, @tableName, @columnName;
  24.  
  25. while @@FETCH_STATUS = 0
  26. begin
  27. -- use dynamic sql to get count of records where column is not null
  28. SET @sql = 'select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName +
  29. '] where [' + @columnName + '] is not null';
  30. -- print @sql; --uncomment for debugging
  31. exec sp_executesql @sql, N'@cnt bigint output', @cnt = @cnt output;
  32.  
  33. INSERT INTO @tempTable SELECT @tableSchema, @tableName, @columnName, @cnt;
  34.  
  35. fetch next FROM columnCursor INTO @tableSchema, @tableName, @columnName;
  36. end
  37.  
  38. close columnCursor;
  39. deallocate columnCursor;
  40.  
  41. SELECT * FROM @tempTable WHERE NotNullCnt = 0;

Report this snippet  

You need to login to post a comment.