Comments posted to this topic are about the item Table-Column Data Profiler

Many thanks for this really useful piece of code. Have been doing this with SSIS Profile task, but this is much more handy. JK

---

Tks,

JK

Glad someone found it helpful. Here is an update that includes (Distinct) Value Counts:

DECLARE @Cat_Sch_TableName varchar(128) = 'HRIS.NTC.D20130201'
-- Initialize fully qualified object name; script can be used like a stored procedure
DECLARE @sqlstring varchar(8000), @ColumnName varchar(128), @ColumnID smallint,
        @SequenceNumber smallint

-- cleanup temp tables        
IF OBJECT_ID('Tempdb..#RawColumns')      IS NOT NULL DROP TABLE #RawColumns
IF OBJECT_ID('Tempdb..#ProfiledColumns') IS NOT NULL DROP TABLE #ProfiledColumns

-- Populate working list of columns
SELECT ColumnID = IDENTITY(int, 1,1),
       COLUMN_NAME       AS ColumnName,
       ORDINAL_POSITION  AS SequenceNumber
      INTO #RawColumns 
     FROM information_schema.COLUMNS  
WHERE TABLE_CATALOG+'.'+TABLE_SCHEMA+'.'+TABLE_NAME=@Cat_Sch_TableName 

-- Initialize output table
CREATE TABLE #ProfiledColumns(
             ColumnName  varchar(128) NOT NULL,
             SequenceNumber varchar(10),
             NullCount int ,
             ModalValue varchar(255),
             ModalCount int,
             ValueCount int,
             MinValue varchar(255),
             MaxValue varchar(255),
             MaxLength smallint )

-- Loop through columns
WHILE (EXISTS(
    SELECT  * 
    FROM #RawColumns 
    WHERE ColumnName IS NOT NULL ))

BEGIN

SELECT @ColumnID       = ColumnID, 
       @ColumnName     = ColumnName,  
       @SequenceNumber = SequenceNumber
FROM #RawColumns -- assign column data to variables

SET @sqlstring=
   'INSERT INTO #ProfiledColumns ' + 
   'SELECT '''+@ColumnName+''',' +
    CAST(@SequenceNumber AS varchar(10)) + ' , ' +
   'SUM(CASE WHEN ['+@ColumnName+'] IS NULL THEN 1 ELSE 0 END) ,'+ 
   '(SELECT TOP 1 ['+@ColumnName+'] FROM '+@Cat_Sch_TableName+' GROUP BY ['+@ColumnName+'] ORDER BY COUNT(*) DESC),' +
   '(SELECT TOP 1 COUNT(*) FROM '+@Cat_Sch_TableName+' GROUP BY ['+@ColumnName+'] ORDER BY COUNT(*) DESC),' +
   'COUNT(DISTINCT ['+@ColumnName+']),' +
   'CAST(MIN(['+@ColumnName+']) AS VARCHAR(255)),'+
   'CAST(MAX(['+@ColumnName+']) AS VARCHAR(255)),'+
   'MAX(LEN(CAST(['+@ColumnName+'] AS VARCHAR(255)))) '+   
   'FROM ' + @Cat_Sch_TableName    -- build query string
                  
DELETE FROM #RawColumns WHERE ColumnID=@ColumnID  -- one column down, next...
EXECUTE (@sqlstring)                              -- execute built query   
END                                               -- End loop block

-- Display data profile with condensed data type
SELECT 
ColumnName,
DATA_TYPE  + 
    CASE WHEN DATA_TYPE LIKE '%char'         THEN '('+LTRIM(CHARACTER_MAXIMUM_LENGTH)+')'
         WHEN COALESCE(NUMERIC_SCALE,0) <> 0 THEN '('+LTRIM(NUMERIC_PRECISION)+','+LTRIM(NUMERIC_SCALE)+')' ELSE ''
END AS DataTypeName,
NullCount,
ModalValue,
ModalCount,
ValueCount,
MinValue,
MaxValue,
MaxLength 
FROM #ProfiledColumns  
JOIN information_schema.COLUMNS
  ON SequenceNumber=ORDINAL_POSITION  
WHERE TABLE_CATALOG+'.'+TABLE_SCHEMA+'.'+TABLE_NAME=@Cat_Sch_TableName 
ORDER BY CAST(SequenceNumber AS SMALLINT)

Handy, but it throws an error on a bit field because you cannot perform a min or max on them

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Thanks forgot about bit. This should work on bit (other data types?) as well...

DECLARE @Cat_Sch_TableName varchar(128) = 'HRIS.dbo.E20130201'
-- Initialize fully qualified object name; script can be used like a stored procedure
DECLARE @sqlstring varchar(8000), @ColumnName varchar(128), @ColumnID smallint,
        @SequenceNumber smallint

-- cleanup temp tables        
IF OBJECT_ID('Tempdb..#RawColumns')      IS NOT NULL DROP TABLE #RawColumns
IF OBJECT_ID('Tempdb..#ProfiledColumns') IS NOT NULL DROP TABLE #ProfiledColumns

-- Populate working list of columns
SELECT ColumnID = IDENTITY(int, 1,1),
       COLUMN_NAME       AS ColumnName,
       ORDINAL_POSITION  AS SequenceNumber
      INTO #RawColumns 
     FROM information_schema.COLUMNS  
WHERE TABLE_CATALOG+'.'+TABLE_SCHEMA+'.'+TABLE_NAME=@Cat_Sch_TableName 

-- Initialize output table
CREATE TABLE #ProfiledColumns(
             ColumnName  varchar(128) NOT NULL,
             SequenceNumber varchar(10),
             NullCount int ,
             ModalValue varchar(255),
             ModalCount int,
             ValueCount int,
             MinValue varchar(255),
             MaxValue varchar(255),
             MaxLength smallint )

-- Loop through columns
WHILE (EXISTS(
    SELECT  * 
    FROM #RawColumns 
    WHERE ColumnName IS NOT NULL ))

BEGIN

SELECT @ColumnID       = ColumnID, 
       @ColumnName     = ColumnName,  
       @SequenceNumber = SequenceNumber
FROM #RawColumns -- assign column data to variables

SET @sqlstring=
   'INSERT INTO #ProfiledColumns ' + 
   'SELECT '''+@ColumnName+''',' +
    CAST(@SequenceNumber AS varchar(10)) + ' , ' +
   'SUM(CASE WHEN ['+@ColumnName+'] IS NULL THEN 1 ELSE 0 END) ,'+ 
   '(SELECT TOP 1 ['+@ColumnName+'] FROM '+@Cat_Sch_TableName+' GROUP BY ['+@ColumnName+'] ORDER BY COUNT(*) DESC),' +
   '(SELECT TOP 1 COUNT(*) FROM '+@Cat_Sch_TableName+' GROUP BY ['+@ColumnName+'] ORDER BY COUNT(*) DESC),' +
   'COUNT(DISTINCT ['+@ColumnName+']),' +
   'CAST(MIN(CAST(['+@ColumnName+'] AS sql_variant)) AS VARCHAR(8000)),'+
   'CAST(MAX(CAST(['+@ColumnName+'] AS sql_variant)) AS VARCHAR(8000)),'+
   'MAX(LEN(CAST(['+@ColumnName+'] AS VARCHAR(255)))) '+   
   'FROM ' + @Cat_Sch_TableName    -- build query string
                  
DELETE FROM #RawColumns WHERE ColumnID=@ColumnID  -- one column down, next...
EXECUTE (@sqlstring)                              -- execute built query   
END                                               -- End loop block

-- Display data profile with condensed data type
SELECT 
ColumnName,
DATA_TYPE  + 
    CASE WHEN DATA_TYPE LIKE '%char'         THEN '('+LTRIM(CHARACTER_MAXIMUM_LENGTH)+')'
         WHEN COALESCE(NUMERIC_SCALE,0) <> 0 THEN '('+LTRIM(NUMERIC_PRECISION)+','+LTRIM(NUMERIC_SCALE)+')' ELSE ''
END AS DataTypeName,
NullCount,
ModalValue,
ModalCount,
ValueCount,
MinValue,
MaxValue,
MaxLength 
FROM #ProfiledColumns  
JOIN information_schema.COLUMNS
  ON SequenceNumber=ORDINAL_POSITION  
WHERE TABLE_CATALOG+'.'+TABLE_SCHEMA+'.'+TABLE_NAME=@Cat_Sch_TableName 
ORDER BY CAST(SequenceNumber AS SMALLINT)

Handy script, Thanks!! Many for the DB's I run into have nvarchar(max) columns. Any feel for how to address these?

Steve Pirazzi
ONEWARE, Inc.
http://www.ONEWARE.com

Hi Todd,

I just tried your script and I like it!

A couple of questions:
1. Can it be modified to loop through all tables in a database automatically and show the results?
2. For the datatypes that it can't handle (uniqueidentifier, image, text) it generates an exception and stops processing. Is it possible that it could simply skip the columns with these datatypes and produce an output of the remaining columns?

I ask because I'm not a strong coder and not sure how to get at this.

Thanks again for an excellent script Todd

Steve

Thanks Steve

I am not guaranteeing anything because I never allow use of varchar(max) and have no way to test it, BUT
for Varchar(max) types to work, change this statement:

CREATE TABLE #ProfiledColumns(
             ColumnName  varchar(128) NOT NULL,
             SequenceNumber varchar(10),
             NullCount int ,
             ModalValue varchar(max),
             ModalCount int,
             ValueCount int,
             MinValue varchar(max),
             MaxValue varchar(max),
             MaxLength smallint 

And this statement:

SET @sqlstring=
   'INSERT INTO #ProfiledColumns ' + 
   'SELECT '''+@ColumnName+''',' +
    CAST(@SequenceNumber AS varchar(10)) + ' , ' +
   'SUM(CASE WHEN ['+@ColumnName+'] IS NULL THEN 1 ELSE 0 END) ,'+ 
   '(SELECT TOP 1 ['+@ColumnName+'] FROM '+@Cat_Sch_TableName+' GROUP BY ['+@ColumnName+'] ORDER BY COUNT(*) DESC),' +
   '(SELECT TOP 1 COUNT(*) FROM '+@Cat_Sch_TableName+' GROUP BY ['+@ColumnName+'] ORDER BY COUNT(*) DESC),' +
   'COUNT(DISTINCT ['+@ColumnName+']),' +
   'CAST(MIN(CAST(['+@ColumnName+'] AS sql_variant)) AS VARCHAR(max)),'+
   'CAST(MAX(CAST(['+@ColumnName+'] AS sql_variant)) AS VARCHAR(max)),'+
   'MAX(LEN(CAST(['+@ColumnName+'] AS VARCHAR(255)))) '+   
   'FROM ' + @Cat_Sch_TableName    -- build query string 

Thanks!

Uhoh..

Yes but not by me yet; you will need to set up an outer loop using a Cursor or a WHILE statement or somesuch. Maybe later...


Try adding this bolded line:

WHERE TABLE_CATALOG+'.'+TABLE_SCHEMA+'.'+TABLE_NAME=@Cat_Sch_TableName 
  AND DATA_TYPE NOT IN ('uniqueidentifier','image','text') -- excluded types

Thanks again Todd,

This is very helpful and much appreciated.