Thanks Lynn but I tried casting the column but I can't get the syntax right.

I will be able to use your script for other situations.

Thanks for pointing out the issues with the code.

I liked your use of CROSS APPLY, etc.

I'm sorry that I was...

Nice code.

What I need in the output is the Table Name, Column name, the Distinct Values & a record count.

TableName Column Name STATUS_OPEN RecordCount

WPA_DIARY_ENTRY ...

Lynn Pettis (5/18/2012)

---

Okay. I was noticing some things about the original query. The first query with the LEFT JOINS; the...

Lynn,

Don't worry about it.

I'm not using it.

Lynn Pettis (5/18/2012)

---

Welsh Corgi (5/18/2012)

---

I wrote some Stored Procedures using CTE but not that much.

How could I convert the code below to CTE?

SELECT s.Name AS SchemaName ,

...

Gazareth (5/18/2012)

---

Something like:

SELECT 'SELECT ' + TableName + ' AS TableName, ' + ColumnName + ' AS ColumnName, CASE WHEN COUNT(DISTINCT ' + ...

Yes, you'll need to do something...

Thanks, it generated the SELECT Statements but I need to return the Table Name and Column Name in the output.

I'm not sure to handle text columns?

If the column is text...

Gazareth.

Thanks for the help!

Unfortunately I get an error for the cte is being referenced.

Msg 208, Level 16, State 1, Line 1

Invalid object name 'cte_firstset'.

If I could just get it...

It is for a Data Warehouse Project.

If the column in the source system is not populated then we don't have to analyze it.

One reason we want to know the cardinality...

Thank you for all of your help.

I need a variation of the script to determine what columns are populated and the cardinality of each row.

Community Technology Preview

CTP is a Release Candidate quality release that is used by Microsoft to get feedback that facilitates change the design and improve the test suite.

BTW, all of the tables have a million or more records.

If I provided sample data then I would need to basically find the offending records. 🙂