Count non-NULL rows and get definition
This is honestly something I wrote for a Stack Overflow Question, but felt that others may well benefit from it. This builds a dynamic SQL statement that provides you with a count of the non-NULL values in a table by column, and provides details on their datatype and if they are a key of some kind.
There is a SELECT/ PRINT statement commented out, which you can use to debug the statement, or help you add to it if you need.
Note that you cannot use aggregation functions on the deprecated data types text and image, therefore the row count will be the number of rows in the table regardless of any of them have the value NULL .
CREATE OR ALTER PROC dbo.CountAndDefinition @Schema sysname, @Table sysname AS
BEGIN
DECLARE @SQL nvarchar(MAX)
SET @SQL = N'WITH Counts AS (' + NCHAR(13) + NCHAR(10) +
N' SELECT @Schema AS SchemaName,' + NCHAR(13) + NCHAR(10) +
N' @Table AS TableName,' +
STUFF((SELECT N',' + NCHAR(13) + NCHAR(10) +
N' COUNT(' + CASE WHEN C.DATA_TYPE IN ('text','image') THEN '1' ELSE QUOTENAME(C.COLUMN_NAME) END + N') AS ' + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_SCHEMA = @Schema
AND C.TABLE_NAME = @Table
--AND C.DATA_TYPE NOT IN ('text','image')
ORDER BY C.ORDINAL_POSITION
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,14,N'') + NCHAR(13) + NCHAR(10) +
N' FROM ' + QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table) + N')' + NCHAR(13) + NCHAR(10) +
N'SELECT V.ColumnName,' + NCHAR(13) + NCHAR(10) +
N' V.NonNullCount,' + NCHAR(13) + NCHAR(10) +
N' ISC.DATA_TYPE + ISNULL(NULLIF(DT.S,''(*)''),'''') AS Datatype,' + NCHAR(13) + NCHAR(10) +
N' K.KeyType' + NCHAR(13) + NCHAR(10) +
N'FROM Counts C' + NCHAR(13) + NCHAR(10) +
N' CROSS APPLY(VALUES' + STUFF((SELECT N',' + NCHAR(13) + NCHAR(10) +
N' (N' + QUOTENAME(C.COLUMN_NAME,'''') + N',C.' + QUOTENAME(C.COLUMN_NAME) + N')'
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME = @Table
--AND C.DATA_TYPE NOT IN ('text','image')
ORDER BY C.ORDINAL_POSITION
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,26,N'') + N')V(ColumnName,NonNullCount)' + NCHAR(13) + NCHAR(10) +
N' JOIN INFORMATION_SCHEMA.COLUMNS ISC ON C.SchemaName = ISC.TABLE_SCHEMA' + NCHAR(13) + NCHAR(10) +
N' AND C.TableName = ISC.TABLE_NAME' + NCHAR(13) + NCHAR(10) +
N' AND V.ColumnName = ISC.COLUMN_NAME' + NCHAR(13) + NCHAR(10) +
N' CROSS APPLY (VALUES(''('' + STUFF(CONCAT('','' + CASE ISC.CHARACTER_MAXIMUM_LENGTH WHEN -1 THEN ''MAX'' ELSE CONVERT(varchar(4),ISC.CHARACTER_MAXIMUM_LENGTH) END,' + NCHAR(13) + NCHAR(10)+
N' '','' + CASE WHEN ISC.DATA_TYPE NOT LIKE ''%int'' THEN CONVERT(varchar(4),ISC.NUMERIC_PRECISION) END,' + NCHAR(13) + NCHAR(10) +
N' '','' + CASE WHEN ISC.DATA_TYPE NOT LIKE ''%int'' THEN CONVERT(varchar(4),ISC.NUMERIC_SCALE) END,' + NCHAR(13) + NCHAR(10) +
N' '','' + CASE WHEN ISC.DATA_TYPE NOT IN (''datetime'',''smalldatetime'') THEN CONVERT(varchar(4),ISC.DATETIME_PRECISION) END),1,1,'''') + '')'')) DT(S)' + NCHAR(13) + NCHAR(10) +
N' OUTER APPLY(SELECT TC.CONSTRAINT_TYPE AS KeyType ' + NCHAR(13) + NCHAR(10) +
N' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC' + NCHAR(13) + NCHAR(10) +
N' JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.TABLE_SCHEMA = KCU.TABLE_SCHEMA' + NCHAR(13) + NCHAR(10) +
N' AND TC.TABLE_NAME = KCU.TABLE_NAME' + NCHAR(13) + NCHAR(10) +
N' AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME' + NCHAR(13) + NCHAR(10) +
N' WHERE KCU.COLUMN_NAME = V.ColumnName' + NCHAR(13) + NCHAR(10) +
N' AND TC.TABLE_SCHEMA = ISC.TABLE_SCHEMA' + NCHAR(13) + NCHAR(10) +
N' AND TC.TABLE_NAME = ISC.TABLE_NAME) K;';
PRINT @SQL; --you will need to use the SELECT here if @SQL is over 4,000 characters
--SELECT @SQL;
EXEC sp_executesql @SQL, N'@Schema sysname,@Table sysname',@Schema = @Schema, @Table = @Table;
END; 
