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!

*"plot for each "TableName" + "ColumnName" TOT number of record and last (max) ColumnName value."*: I have no idea what that means. Can you include in your question a table as you would like the output to be? — trincot, Aug 24 '17 at 17:21
Just added. Basically from the main query I need to iterate per each TableName and per each ColumnName and obtain TOT number of record and last value. — user3925023, Aug 24 '17 at 17:40

score 1 · Accepted Answer · answered Aug 24 '17 at 18:26

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

Is there a way to avoid creation of table?I don't have rights to do so.Thx — user3925023, Aug 24 '17 at 22:09
see this: https://jasonstrate.com/2013/05/21/security-questions-what-permissions-are-required-to-create-temporary-tables/ I think every user has right to create temporary tables! — Niloct, Aug 24 '17 at 22:30

score 1 · Answer 2 · answered Aug 25 '17 at 02:44

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

It is not possible.

You need to call a dynamic sql statement

SET @sql = 'SELECT @MaxID = MAX('+@ColumnName+') FROM '+@TableName

separately for every line of your resultset and as functions cannot contain dynamic SQL it is not possible to write it as one select statement in SQL Server. You cannot call dynamic SQL or a stored procedure for every single row separately in one query.

In Oracle there are commands like BULK COLLECT and FORALL that allow combining resultsets with row-by-row processing, but I am not aware that anything like that exists in SQL Server.

SQL Recursive index finding query

2 Answers2