Profile columns for indexes

Every once in a while, as part of a consulting project I need to investigate all the different columns and datatypes used within the tables. Often in order to achieve this I would write the SQL script individually but since I’ve been doing it regularly recently I’ve decided to automate the script. The blue script iterates through every table in the database and every column within that table and profiles it in terms of the values the data type the length and the row counts. Based on the output of this data we can then decide which columns need to be indexed and if any data types need to be tweaked.

IF NOT EXISTS (SELECT 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME ='COLUMNPROFILE' and TABLE_SCHEMA='DBO')
BEGIN
CREATE TABLE [dbo].[COLUMNPROFILE](
		[TableName] varchar(100) NOT NULL,
    [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,
	[Len] int not null,
    [Datatype] [varchar](100) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END

--truncate table [COLUMNPROFILE]
-- drop table #temp

select t.TABLE_SCHEMA , t.TABLE_NAME , ROW_NUMBER() over (order by t.TABLE_SCHEMA , t.TABLE_NAME) as rid  into #temp
from INFORMATION_SCHEMA.TABLES t
where t.TABLE_TYPE like'%table%'
order by t.TABLE_SCHEMA , t.TABLE_NAME 
select * from #temp

declare @cnt int =1 
WHILE @cnt <= (select max(rid) from #temp )
BEGIN
				declare @tablename varchar(100) = (select TABLE_NAME from #temp where rid = @cnt)
				declare @schemaname varchar(100) =(select TABLE_SCHEMA from #temp where rid = @cnt)

				declare @querytext table ( querytext varchar(4000), id int identity(1,1))

				insert into @querytext
				select  'INSERT INTO '+DB_NAME()+'.dbo.ColumnProfile Select '+char(39)+@schemaname+'-'+ @tablename+char(39)+' as Tablename ,'+Char(39)+QUOTENAME(Column_name)+char(39)+' as ColName,MAX('+QUOTENAME(Column_name)+') as MaxVal , MIN('+QUOTENAME(Column_name)+') as MinVal, COUNT('+QUOTENAME(Column_name)+') as CountRows,COUNT(DISTINCT('+QUOTENAME(Column_name)+')) as DistinctCount ,COUNT(*) as TotalCCount ,MAX(LEN('+QUOTENAME(Column_name)+')) as Len ,'+char(39)+DATA_TYPE+char(39)+' as Datatype from '+DB_NAME()+'.'+@schemaname+'.'+@tablename+'' as querytext  from INFORMATION_SCHEMA.COLUMNS
				where TABLE_NAME=@tablename and TABLE_SCHEMA=@schemaname

				declare @cnt2 int =1 
				while @cnt2 <=(select max(id) from @querytext)
				BEGIN
				declare @sql nvarchar(max) = ( select querytext from @querytext where id =@cnt2)
				print @sql
				/**************UNCOMMENT THE NEXT LINE WHEN YOU ARE READY TO EXEUTE THE SCRIPT******/
				--exec master..sp_executesql @sql
				--print @tablename
				set @cnt2 += 1
				END 
				delete from @querytext
				--Print cast(@cnt as varchar(100))+'*****'
set @cnt += 1 
END