Kimball ETL (subsystem 1) – Data Profiling via TSQL

I have an earlier post covering Kimball ETL (subsystem 1) – Data Profiling via SIS Data Flow, here is the script for TSQL> 

This script will generate a report providing all normally expected statistics for data profiling as follows:

 [SchemaName],

 [TableName],

 [ColumnName],

 [RecordCount],

 [DistinctDomainCount],

 [MinDomain],

 [MaxDomain],

 [MinColumnPattern],

 [MaxColumnPattern],

 [MinDomainLength],

 [MaxDomainLength],

 [NullDomainCount],

 [BlankDomainCount]

Figure 1. Results for Adventure Works DimGeography table . PostalCode column.

Simply replace the values in the following and run.

SET @tablename ='DimGeography'
SET @columnname ='PostalCode'
SET @schemaname='dbo'

Also note the script will create a function in the dbo scheam unless modified.