Pfthb

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 
 INFORMATION_SCHEMA.COLUMNS

However, this throws an error:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.ExecuteScalarToInt", or the name is ambiguous.

Hmmm. I deleted the dbo.ExecuteScalarToInt part:

SELECT 
 TABLE_NAME,
 COLUMN_NAME,
 ('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)) AS Percentage 
FROM 
 INFORMATION_SCHEMA.COLUMNS
ORDER BY 
 TABLE_NAME ASC, COLUMN_NAME ASC

This works, but for the percentage column, a SQL query shows up. Tried the resulting SQL query--it works.

Tried substituting "exec", "sp_executesql", and "exec sp_executesql".
Got errors.

Here is what I'm imagining what the output should look similar to:

TableName TableRows ColumnName PctPopulatedRows PctNULLs PctBlanks PctZeros
LineItems 4,649,764 InventryNum 100.0% 0.0% 0.0% 0.0%
LineItems 4,649,764 Description 99.9% 0.0% 0.1% 0.0%
LineItems 4,649,764 Price 99.7% 0.0% 0.1% 0.2%
.
.
.
Invoices 385,532 InvoiceNum 100.0% 0.0% 0.0% 0.0% 
Invoices 385,532 DateTime 99.6% 0.2% 0.1% 0.1% 
Invoices 385,532 TotalAmt 99.8% 0.0% 0.2% 0.0% 
.
.
.
Customers 64,906 CustomerNum 100.0% 0.0% 0.0% 0.0% 
Customers 64,906 CustStreet 99.8% 0.0% 0.2% 0.0% 
Customers 64,906 CustZip 99.7% 0.1% 0.2% 0.0% 

Tired of banging my head against this. Can anyone straighten me out to get closer to a solution?

Many thanks for taking a look.