0

I tried something like this, which did not work:

SELECT COLUMN_NAME, COUNT(COLUMN_NAME) AS COUNT_NOT_NULL FROM myDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTable';

Obviously, the "information-meta-table" doesn't have the data from myTable to count. So do I need some sort of JOIN? Do I need to declare some variable and use some iteration?

The weird part is that this solution works for some tables, but not for others (it counts NULLs): https://stackoverflow.com/a/24411738/8055476

Ramon Leonardi
  • 1
  • 1
  • 1
    The answer you have linked used dynamic SQL, you are not. Actually think through what your script is doing and you will see why it is not working. – iamdave May 24 '17 at 13:46
  • Here's a link to the MS Docs on [dynamic SQL](https://learn.microsoft.com/en-us/sql/odbc/reference/dynamic-sql). – David Rushton May 24 '17 at 14:06
  • @RamonLeonardi Hope that wolud get the result as your requirement –  May 24 '17 at 14:21
  • That solution you linked (great solution btw) only works if you are in the same database when you run it, and where the schema is the default (usually dbo). If your tables exist in a different schema then you would need to edit the script slightly or it will just return no results. Once you have that working it shouldn't be too hard to change it to count non-NULLs instead of NULLs? – Richard Hansell May 24 '17 at 15:52

1 Answers1

0

First I created a table with some sample data 

IF OBJECT_ID('COuntNotNull') IS NOT NULL
    Drop Table COuntNotNull
    ;With cte(Column1 , Column2 , Column3 , Column4 )
    AS
    (
    SELECT  'X'     ,   ' X'    ,   NULL  ,    'X'    Union all
    SELECT NULL   ,   NULL  ,    'X'    ,    'X'       Union all
    SELECT NULL   ,   NULL  ,   NULL  ,   NULL  
    )

    SELECT * INTO  COUNTNOTNULL FROM cte

The below code get the column names dynamically For given table and get count of non null values

DECLARE @DynamicColms NVARCHAR(max)
    ,@CaseDynamicColms NVARCHAR(max)
    ,@Sql NVARCHAR(max)
    ,@TableName VARCHAR(100) = 'COuntNotNull'--Here give Your TableName 

SELECT @DynamicColms = STUFF((
            SELECT ', ' + COLUMN_NAME
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = @TableName
            FOR XML PATH('')
            ), 1, 1, '')

--SELECT @DynamicColms
SELECT @CaseDynamicColms = STUFF((
            SELECT '+ ' + 'CASE WHEN ' + COLUMN_NAME + ' IS NOT NULL THEN 1 ELSE 0 END'
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = @TableName
            FOR XML PATH('')
            ), 1, 1, '')

--SELECT @CaseDynamicColms
SET @Sql = 'SELECT ' + @DynamicColms + ',' + @CaseDynamicColms + CHAR(13) + CHAR(10) + '  AS COUNT_NOT_NULL FROM ' + @TableName

PRINT @Sql

EXECUTE (@Sql)

Result

Column1 Column2 Column3 Column4   COUNT_NOT_NULL
-------------------------------------------------
X       X       NULL     X          3
NULL    NULL    X        X          2
NULL    NULL    NULL     NULL       0