SQL Server: quick and dirty data validation query?
2 Getting frequent updates on a database that is going through major changes. Need a quick way to check for abnormalities in the data. Over 1,000 tables; about 200 actually being used. Would like to have a list of the tables with the most records at the top. Found and modified this code that seems to work: select object_name(object_id) AS TableName, sum(rows) AS TableRowCount from sys.partitions where index_id in (0, 1) group by object_id order by TableRowCount desc However, it would be useful to also have an indication if columns are populated properly. Would be great to have percentages of valid rows as well as percentages of NULLs, blanks and zeros. Stumbled across this snippet: SELECT TABLE_NAME, COLUMN_NAME, dbo.ExecuteScalarToInt('SELECT ISNULL(SUM(CASE WHEN ' + QUOTENAME(COLUMN_NAME) + ' IS NULL THEN 1 ELSE 0 END) * 100.00/count(*), 0) Percentage FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) Percentage FROM INFORM
