Cfrgtkky

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.