CODEWITHSUNDEEP
sqlsql-serverindexing
user3925023
user3925023

Reputation: 687

SQL Recursive index finding query

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:

enter image description here

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: enter image description here

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

Upvotes: 3

Views: 519

Answers (2)

Vojtěch Dohnal
Vojtěch Dohnal

Reputation: 8104

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.

Upvotes: 1

Niloct
Niloct

Reputation: 10015

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

Upvotes: 1

Related Questions