Hi,

I'm trying to figure out the best way to generate a t-sql query that will return a result set of the columns in a table along with each distinct value and a rowcount of each distinct value.

Example of what i'm looking for.

--
Movies table has the following columns:

movieid, title, movierating, genre, director


I started out with this:


--
select MovieRating, count(MovieRating) as total
from dbo.Movies
group by MovieRating
order by total desc
--

...and get the following result set:

MovieRating total
------------------ -----------
R 2100
PG 9050
PG-13 7000
Unrated 675
G 2975
NC-17 107

-----

That's nice but what i'm trying to get in one script is:

column_name distinct_value total
------------- ---------------- --------
movierating R 2100
movierating PG 9050
movierating PG-13 7000
movierating Unrated 675
.
.
.
.
title Jaws 1
title Star Wars I 1
title Star Trek 4 3
.
.
.
.
genre Comedy 3005
genre Adventure 1432
genre Drama 2500
.
.
.........etc.

Any ideas would be appreciated. Thanks.

Solution to your problem:

-------------------------------------------------------------------------
CREATE PROCEDURE getColumnsDistinctValueCount
@TableName VARCHAR(255)
AS
BEGIN

    DECLARE @sqlString VARCHAR(8000)

    /*Building required dynamic SQL string*/
    SELECT @sqlString=Isnull(@sqlString+' UNION '+'SELECT '''+Column_Name+''' AS column_name, cast('+Column_Name+' AS nvarchar) distinct_value, COUNT(*) AS total FROM '+TABLE_NAME+' GROUP BY '+Column_Name,
                        'SELECT '''+Column_Name+''' AS column_name, cast('+Column_Name+' AS nvarchar) distinct_value, COUNT(*) AS total FROM '+TABLE_NAME+' GROUP BY '+Column_Name)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TableName

    /*Executing generated dynamic sql to get the required resulsts*/
    EXEC(@sqlString)

END

EXEC getColumnsDistinctValueCount 'Movies'
-------------------------------------------------------------------------

Cheers,
Pooja Falor
Pune, India

That works good. Exception to this noted below:

Server: Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Nevertheless, I found a good inexpensive data profiling tool that goes above and beyond doing this manually. Saved me a ton of time and was extremely insightful. Check out:

http://www.datiris.com/

Thanks for your help.

Paul