question

I think i figured out a solution: -------------------------------------------------------------------- --checks if the temp table exists and re-creates it for each run ---------------------------------------------------------------------- if exists ( select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and name LIKE '#columnCount%' ) DROP TABLE #columnCount GO create table #columnCount ( TableName varchar(100), ColumnName varchar(100), maxValue varchar(100), valueCount bigint ) GO --------------------------------------------------------------- --BEGIN CURSOR CODE to pull all tables and column names and put into temp table ---------------------------------------------------------------- DECLARE @tableName varchar(150) DECLARE @columnName varchar(150) DECLARE @sql varchar(max) DECLARE @metric cursor SET @metric = CURSOR FOR select a.name, b.name from sys.objects a JOIN sys.columns b on a.object_id = b.object_id WHERE a.type = 'U' and a.schema_id = 1 OPEN @metric FETCH NEXT FROM @metric INTO @tableName, @columnName WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = ' INSERT INTO #columnCount (tableName, columnName, maxValue, valueCount) SELECT TOP 1 ''' +@tableName+ ''', ''' +@columnName+ ''', ' +@columnName+ ', count(*) as Count from ' +@tableName+ ' group by ' +@columnName+ ' order by count(*) desc ' EXEC (@sql) FETCH NEXT FROM @metric INTO @tableName, @columnName END CLOSE @metric DEALLOCATE @metric select * from #columnCount order by tableName, columnName