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.

IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME ='ColumnMetadata' AND TABLE_SCHEMA ='dbo')
BEGIN
DROP TABLE [dbo].[ColumnMetadata]
END
GO


CREATE TABLE [dbo].[ColumnMetadata](
[TABLE_CATALOG] [nvarchar](128) NULL,
[TABLE_SCHEMA] [nvarchar](128) NULL,
[TABLE_NAME] [sysname] NOT NULL,
[COLUMN_NAME] [sysname] NULL,
[ORDINAL_POSITION] [int] NULL,
[HasDefault] [int] NOT NULL,
[IS_NULLABLE] [varchar](3) NULL,
[DATA_TYPE] [nvarchar](128) NULL,
[CHARACTER_MAXIMUM_LENGTH] [int] NULL,
[NUMERIC_PRECISION] [tinyint] NULL,
[NUMERIC_SCALE] [int] NULL,
[UnqRowsCount] [int] NULL,
[RowsCount] [int] NULL,
[MaxLengthCount] [int] NULL,
[Id] [int] IDENTITY(1,1) NOT NULL
) 


GO




select CONCAT('INSERT INTO [dbo].[ColumnMetadata] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [HasDefault], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [NUMERIC_PRECISION], [NUMERIC_SCALE], [UnqRowsCount], [RowsCount], [MaxLengthCount])',
'select '+char(39)+[TABLE_CATALOG]+char(39)+',',
+char(39)+[TABLE_SCHEMA]+char(39)+',',
+char(39)+[TABLE_NAME]+char(39)+',',
+char(39)+[COLUMN_NAME]+char(39)+',',
+char(39)+Cast([ORDINAL_POSITION] as varchar(100))+char(39)+',',
+char(39)+CASE WHEN [COLUMN_DEFAULT] IS NULL THEN '0' ELSE '1' END+char(39)+',',
+char(39)+[IS_NULLABLE]+char(39)+',',
+char(39)+[DATA_TYPE]+char(39)+',',
+char(39)+Cast([CHARACTER_MAXIMUM_LENGTH] as varchar(100))+char(39)+',',
+char(39)+cast(ISNULL([NUMERIC_PRECISION],0) as varchar(100))+char(39)+',',
+char(39)+cast(ISNULL([NUMERIC_SCALE],0) as varchar(100))+char(39)+',',
'COUNT(DISTINCT('+QUOTENAME([COLUMN_NAME])+')) , COUNT('+QUOTENAME([COLUMN_NAME])+') , MAX(LEN('+QUOTENAME([COLUMN_NAME])+')) FROM '+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+'')
FROM INFORMATION_SCHEMA.COLUMNS