DECLARE @Cat_Sch_TableName varchar(128) = 'HRIS.NTC.D20130201'-- Initialize fully qualified object name; script can be used like a stored procedureDECLARE @sqlstring varchar(8000), @ColumnName varchar(128), @ColumnID smallint, @SequenceNumber smallint-- cleanup temp tables IF OBJECT_ID('Tempdb..#RawColumns') IS NOT NULL DROP TABLE #RawColumnsIF OBJECT_ID('Tempdb..#ProfiledColumns') IS NOT NULL DROP TABLE #ProfiledColumns-- Populate working list of columnsSELECT 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 tableCREATE 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 columnsWHILE (EXISTS( SELECT * FROM #RawColumns WHERE ColumnName IS NOT NULL ))BEGINSELECT @ColumnID = ColumnID, @ColumnName = ColumnName, @SequenceNumber = SequenceNumberFROM #RawColumns -- assign column data to variablesSET @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 typeSELECT 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)
DECLARE @Cat_Sch_TableName varchar(128) = 'HRIS.dbo.E20130201'-- Initialize fully qualified object name; script can be used like a stored procedureDECLARE @sqlstring varchar(8000), @ColumnName varchar(128), @ColumnID smallint, @SequenceNumber smallint-- cleanup temp tables IF OBJECT_ID('Tempdb..#RawColumns') IS NOT NULL DROP TABLE #RawColumnsIF OBJECT_ID('Tempdb..#ProfiledColumns') IS NOT NULL DROP TABLE #ProfiledColumns-- Populate working list of columnsSELECT 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 tableCREATE 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 columnsWHILE (EXISTS( SELECT * FROM #RawColumns WHERE ColumnName IS NOT NULL ))BEGINSELECT @ColumnID = ColumnID, @ColumnName = ColumnName, @SequenceNumber = SequenceNumberFROM #RawColumns -- assign column data to variablesSET @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 typeSELECT 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)
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
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
WHERE TABLE_CATALOG+'.'+TABLE_SCHEMA+'.'+TABLE_NAME=@Cat_Sch_TableName AND DATA_TYPE NOT IN ('uniqueidentifier','image','text') -- excluded types