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