I am trying to find total number of null value from multiple columns.
SQL query:
SELECT
COUNT( * )-COUNT(externalData) As A,
COUNT( * )-COUNT(CID) As B,
COUNT( * )-COUNT(PID) As C
FROM
dbo.External_Member_Data;
Which returns the correct output.
My issue is: I have more than 20 tables, each table has more than 20 columns. I want count of null value for all column. Do I need to write individual query for all the table.
Is there any best approach?
If statistics running on the database, table and columns involved, for each table you could try:
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CURRENT_ROWS, NUMBER_DISTINCT_VALUES, NUMBER_NULLS
FROM SchemaDB.SYSCOLUMNSTAT
WHERE TABLE_NAME = 'TableName'
OR multi-tables:
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CURRENT_ROWS, NUMBER_DISTINCT_VALUES, NUMBER_NULLS
FROM SchemaDB.SYSCOLUMNSTAT
WHERE TABLE_NAME IN ('TableName1','TableName2',...,'TableName_n')
or possibly a where clause on TABLE_SCHEMA. Documentation says NUMBER_NULLS is an estimate though.
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
SELECT '
' +
STUFF((
SELECT ', [' + c.name + '] = ' + CASE WHEN c.is_nullable = 0 THEN '0' ELSE 'COUNT(*) - COUNT([' + c.name + '])' END
FROM sys.columns c
WHERE c.[object_id] = o.[object_id]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, 'SELECT ''' + SCHEMA_NAME(o.[schema_id]) + '.' + o.name + ''', COUNT(*), ') + '
FROM [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']'
FROM sys.objects o
WHERE o.[type] = 'U'
AND o.is_ms_shipped = 0
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
PRINT @SQL
EXEC sys.sp_executesql @SQL
output -
SELECT 'Production.ProductCostHistory', COUNT(*), [ProductID] = 0, [StartDate] = 0, [EndDate] = COUNT(*) - COUNT([EndDate]), [StandardCost] = 0, [ModifiedDate] = 0
FROM [Production].[ProductCostHistory]
SELECT 'Production.ProductDescription', COUNT(*), [ProductDescriptionID] = 0, [Description] = 0, [rowguid] = 0, [ModifiedDate] = 0
FROM [Production].[ProductDescription]
SELECT 'Sales.ShoppingCartItem', COUNT(*), [ShoppingCartItemID] = 0, [ShoppingCartID] = 0, [Quantity] = 0, [ProductID] = 0, [DateCreated] = 0, [ModifiedDate] = 0
FROM [Sales].[ShoppingCartItem]
SELECT 'Production.ProductDocument', COUNT(*), [ProductID] = 0, [DocumentNode] = 0, [ModifiedDate] = 0
FROM [Production].[ProductDocument]
SELECT 'dbo.DatabaseLog', COUNT(*), [DatabaseLogID] = 0, [PostTime] = 0, [DatabaseUser] = 0, [Event] = 0, [Schema] = COUNT(*) - COUNT([Schema]), [Object] = COUNT(*) - COUNT([Object]), [TSQL] = 0, [XmlEvent] = 0
FROM [dbo].[DatabaseLog]
results -
BusinessEntityID PersonType NameStyle Title FirstName MiddleName LastName Suffix EmailPromotion AdditionalContactInfo Demographics rowguid ModifiedDate
------------- ----------- ---------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -------------- --------------------- ------------ ----------- ------------
Person.Person 19972 0 0 0 18963 0 8499 0 19919 0 19962 0 0 0
SELECT 'SELECT ...' ...