SQL递归索引查询

Question

SQL递归索引查询

3

我有一个查询，列出了我的数据库中所有的表以及它们各自的索引：

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

我得到了这样的输出：

TableName -> 包含数据库中的表名
ColumName -> 包含每个表的索引列名称

我的目标是为每个 "TableName" + "ColumnName" 绘制记录的 TOT 数和最后（最大）的 ColumnName 值。

基本上，这就是我想要获取的选项卡格式输出：

您有任何想法如何在单个查询中递归生成此结果吗？
谢谢！

- user3925023

1

"为每个“TableName”+“ColumnName”的图绘制记录总数和最后（最大）的ColumnName值。"：我不知道这是什么意思。你能否在问题中包含一个表格，以说明你希望输出的内容？ - trincot

刚刚添加。基本上从主查询中，我需要针对每个TableName和每个ColumnName进行迭代，并获得记录的TOT数量和最后一个值。 - user3925023

@user3925023 - 每个表都有主键吗？ - Poonam

2个回答

1

你有没有想过如何在一次查询中递归生成这个结果？

这是不可能的。

你需要调用动态SQL语句。

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

针对结果集的每一行分别进行处理是必须的，而且由于函数不能包含动态 SQL ，因此不可能在 SQL Server 中将其编写为一个 select 语句。你无法在一个查询中单独为每一行调用动态 SQL 或存储过程。

在 Oracle 中，有像 BULK COLLECT 和 FORALL 这样的命令，可以将结果集与逐行处理相结合，但我不知道 SQL Server 中是否存在类似的东西。

- Vojtěch Dohnal

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Niloct · Accepted Answer

好的，这可能足够快以在您的设置中运行，并且是我最好的尝试。存在涉及列类型和max()聚合的问题，这决定了max_value应该是一个varchar。

只需确保在创建和执行此过程之前使用use your_database_name，对于应用程序调用而言，这类似于单个查询。

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