SQL Recursive index finding query

Question

I have query that list all tables in my DB with their respective indexes:

SELECT 
     TableName = t.name,
     IndexName = ind.name,
     IndexId = ind.index_id,
     ColumnId = ic.index_column_id,
     ColumnName = col.name,
     ind.*,
     ic.*,
     col.* 
FROM 
     sys.indexes ind 
INNER JOIN 
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
INNER JOIN 
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
     sys.tables t ON ind.object_id = t.object_id 
WHERE 
     ind.is_primary_key = 0 
     AND ind.is_unique = 0 
     AND ind.is_unique_constraint = 0 
     AND t.is_ms_shipped = 0

I obtain an output like this:

TableName -> contain Table names in DB ColumName -> contain Index column name for each table in DB

My target is to plot for each "TableName" + "ColumnName" TOT number of record and last (max) ColumnName value.

Basically that's the output in tab format I'd like to obtain:

Do you have any idea how to recursively generate this result in a single query? Thanks!

Answer 1

Well, this may be fast enough to run in your setup, and was my best try. There are problems involving type of columns and max() aggregate, which dictates that the max_value should be an varchar.

Just make sure you use your_database_name before creating and executing this procedure, which for the application invocation is similar to a single query.

create proc stp_generateColInfo as

declare
 @tablename varchar(1000),
 @columnname varchar(1000)

set nocount on

create table #TMP_DDL (
    TableName varchar(1000), 
    ColumnName varchar(1000),
    TOTAL int,
    MAX_VALUE varchar(8000)
);

INSERT INTO #TMP_DDL
SELECT 
     TableName = t.name,
     ColumnName = col.name,
     0 as TOTAL,
     '' as MAX_VALUE
FROM 
     sys.indexes ind 
INNER JOIN 
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
INNER JOIN 
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
     sys.tables t ON ind.object_id = t.object_id 
WHERE 
     ind.is_primary_key = 0 
     AND ind.is_unique = 0 
     AND ind.is_unique_constraint = 0 
     AND t.is_ms_shipped = 0


declare cs cursor local static forward_only for
    select TableName, ColumnName from #TMP_DDL

open cs
fetch next from cs into @tablename, @columnname
while @@FETCH_STATUS=0
begin
    exec('
    declare @total int, @max_value varchar(8000)
    select @total=count(*), @max_value=max(IsNull(' + @columnname + ',0)) from ' + @tablename + '
    update #TMP_DDL set total=@total, max_value=@max_value where TableName=''' + @tablename + ''' and ColumnName=''' + @columnname + ''''
    )
    fetch next from cs into @tablename, @columnname
end

close cs
deallocate cs

set nocount off

--Add joins here to select additional columns :)
select * from #TMP_DDL

go