by: jayanth.kurupPosted on:

Query to quickly profile a column

Every once in a while, there is a need to profile columns in a table. typically as part of a redesign, the below query help quickly profile some important aspects of the column such as the name, datatype , min , max , count and count distinct values.

The query generates the profile which can then be saved into the table Column profile for future reference.

CREATE TABLE [dbo].[COLUMNPROFILE](
    [ColName] [varchar](100) NOT NULL,
    [MaxVal] [varchar](max) NULL,
    [MinVal] [varchar](max) NULL,
    [CountRows] [varchar](100) NULL,
    [DistinctCount] [varchar](100) NULL,
    [TotalCCount] [varchar](100) NULL,
    [Datatype] [varchar](100) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

declare @tablename varchar(100) ='Example'
declare @schemaname varchar(100) ='dbo'
select  'INSERT INTO ColumnProfile Select '+Char(39)+Column_name+char(39)+' as ColName,MAX('+Column_name+') as MaxVal , MIN('+Column_name+') as MinVal, COUNT('+Column_name+') as CountRows,COUNT(DISTINCT('+Column_name+')) as DistinctCount ,COUNT(*) as TotalCCount , '+char(39)+DATA_TYPE+char(39)+' as Datatype from '+@tablename+'' from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=@tablename and TABLE_SCHEMA=@schemaname

Leave a Reply