CODEWITHSUNDEEP
sql-servert-sql
Adarsh Srivastava
Adarsh Srivastava

Reputation: 11

Need to filter out all columns which have null,0 or empty values in sql server from a group of tables

I have a bunch of tables about 50 of them and from all of those tables I am trying to figure out a way to filter all the columns which have 0 null or empty values in them. Not sure if sys.tables or Information_schema.tables would have any such function to suffice this.Like here the last column have null values for all rows so for each table I supply in my query I need all such column with table name

Upvotes: 1

Views: 789

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67301

The following command will use NULLIF two times to deal with 0 and with '' and with NULL all the same. COUNT() will not count rows with NULL. If the count returns 0, then there is no other content in this column: 

SELECT COUNT(NULLIF(NULLIF(CAST(tbl.SomeField AS NVARCHAR(MAX)),''),'0'))
FROM SomeTable AS tbl;

The nice part is: You do not have to type this for all columns... The following code will generate this command for all columns of a given table and use EXEC() for its execution:

DECLARE @cmd NVARCHAR(MAX);
DECLARE @tableName NVARCHAR(MAX)='SomeTable'
SELECT @cmd='SELECT '
      + STUFF(
        (
        SELECT ',COUNT(NULLIF(NULLIF(CAST(' +  QUOTENAME(COLUMN_NAME) + ' AS NVARCHAR(MAX)),''''),''0''))'
             + ' AS ' + QUOTENAME('CountOf_' + COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME=@tableName
        FOR XML PATH(''),TYPE).value(N'.',N'nvarchar(max)'),1,1,'')
      + ' FROM ' + QUOTENAME(@tableName) + ';'

PRINT @cmd; --check the output in a query window
EXEC(@cmd);

You might want to use a WHERE clause to reduce this to columns with specific types...

It should be rather easy to take this further to deal with all your tables in a (CURSOR) loop dynamically.

Upvotes: 0

Melina Sharma
Melina Sharma

Reputation: 130

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db_name'
  [AND table_name LIKE 'wild']

SHOW TABLES
  FROM db_name
  [LIKE 'wild']

Upvotes: 1

Related Questions