时间戳

首页 > 解决方案 > 如何通过 SQL Server 上的数据库名称获取所有数据库的索引名称

sql - 如何通过 SQL Server 上的数据库名称获取所有数据库的索引名称

问题描述

标题几乎总结了我正在寻找的东西。所以我正在寻找一些关于 sql 索引的元信息，例如。表名、架构名、数据库名。

SELECT 
 TableName = t.name,
 IndexName = ind.name,
 IndexId = ind.index_id,
 ColumnId = ic.index_column_id,
 ColumnName = col.name,
 SchemaName = s.name
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
INNER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
 ind.name = 'testindex'
ORDER BY 
 t.name, ind.name, ind.index_id, ic.index_column_id;

到目前为止，我已经设法为索引获取 TableName 和 SchemaName，但是我不知道如何获取数据库名称。

标签： sqlsql-server

解决方案

使用Aaron Bertrand 的脚本来制作更可靠和灵活的sp_MSforeachdb，您可以使用带有修改的查询。

您将需要使用它、游标或其他方法来遍历服务器上的所有数据库。这是我见过的最好的之一，因为正如 Aaron 所指出的，微软的版本可以跳过数据库。

sp_foreachdb 方法

CREATE PROCEDURE dbo.sp_foreachdb
    @command NVARCHAR(MAX),
    @replace_character NCHAR(1) = N'?',
    @print_dbname BIT = 0,
    @print_command_only BIT = 0,
    @suppress_quotename BIT = 0,
    @system_only BIT = NULL,
    @user_only BIT = NULL,
    @name_pattern NVARCHAR(300) = N'%', 
    @database_list NVARCHAR(MAX) = NULL,
    @recovery_model_desc NVARCHAR(120) = NULL,
    @compatibility_level TINYINT = NULL,
    @state_desc NVARCHAR(120) = N'ONLINE',
    @is_read_only BIT = 0,
    @is_auto_close_on BIT = NULL,
    @is_auto_shrink_on BIT = NULL,
    @is_broker_enabled BIT = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @sql NVARCHAR(MAX),
        @dblist NVARCHAR(MAX),
        @db NVARCHAR(300),
        @i INT;

    IF @database_list > N''
    BEGIN
        ;WITH n(n) AS 
        (
            SELECT ROW_NUMBER() OVER (ORDER BY s1.name) - 1
            FROM sys.objects AS s1 
            CROSS JOIN sys.objects AS s2
        )
        SELECT @dblist = REPLACE(REPLACE(REPLACE(x,'</x><x>',','),
        '</x>',''),'<x>','')
        FROM 
        (
            SELECT DISTINCT x = 'N''' + LTRIM(RTRIM(SUBSTRING(
            @database_list, n,
            CHARINDEX(',', @database_list + ',', n) - n))) + ''''
            FROM n WHERE n <= LEN(@database_list)
            AND SUBSTRING(',' + @database_list, n, 1) = ','
            FOR XML PATH('')
        ) AS y(x);
    END

    CREATE TABLE #x(db NVARCHAR(300));

    SET @sql = N'SELECT name FROM sys.databases WHERE 1=1'
        + CASE WHEN @system_only = 1 THEN 
            ' AND database_id IN (1,2,3,4)' 
            ELSE '' END
        + CASE WHEN @user_only = 1 THEN 
            ' AND database_id NOT IN (1,2,3,4)' 
            ELSE '' END
        + CASE WHEN @name_pattern <> N'%' THEN 
            ' AND name LIKE N''%' + REPLACE(@name_pattern, '''', '''''') + '%''' 
            ELSE '' END
        + CASE WHEN @dblist IS NOT NULL THEN 
            ' AND name IN (' + @dblist + ')' 
            ELSE '' END
        + CASE WHEN @recovery_model_desc IS NOT NULL THEN
            ' AND recovery_model_desc = N''' + @recovery_model_desc + ''''
            ELSE '' END
        + CASE WHEN @compatibility_level IS NOT NULL THEN
            ' AND compatibility_level = ' + RTRIM(@compatibility_level)
            ELSE '' END
        + CASE WHEN @state_desc IS NOT NULL THEN
            ' AND state_desc = N''' + @state_desc + ''''
            ELSE '' END
        + CASE WHEN @is_read_only IS NOT NULL THEN
            ' AND is_read_only = ' + RTRIM(@is_read_only)
            ELSE '' END
        + CASE WHEN @is_auto_close_on IS NOT NULL THEN
            ' AND is_auto_close_on = ' + RTRIM(@is_auto_close_on)
            ELSE '' END
        + CASE WHEN @is_auto_shrink_on IS NOT NULL THEN
            ' AND is_auto_shrink_on = ' + RTRIM(@is_auto_shrink_on)
            ELSE '' END
        + CASE WHEN @is_broker_enabled IS NOT NULL THEN
            ' AND is_broker_enabled = ' + RTRIM(@is_broker_enabled)
        ELSE '' END;

        INSERT #x EXEC sp_executesql @sql;

        DECLARE c CURSOR 
            LOCAL FORWARD_ONLY STATIC READ_ONLY
            FOR SELECT CASE WHEN @suppress_quotename = 1 THEN 
                    db
                ELSE
                    QUOTENAME(db)
                END 
            FROM #x ORDER BY db;

        OPEN c;

        FETCH NEXT FROM c INTO @db;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @sql = REPLACE(@command, @replace_character, @db);

            IF @print_command_only = 1
            BEGIN
                PRINT '/* For ' + @db + ': */'
                + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
                + @sql 
                + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10);
            END
            ELSE
            BEGIN
                IF @print_dbname = 1
                BEGIN
                    PRINT '/* ' + @db + ' */';
                END

                EXEC sp_executesql @sql;
            END

            FETCH NEXT FROM c INTO @db;
    END

    CLOSE c;
    DEALLOCATE c;
END

Proc的执行

EXEC sp_foreachdb
@command = 'SELECT
 DBNAME = ''?'', 
 TableName = t.name,
 IndexName = ind.name,
 IndexId = ind.index_id,
 ColumnId = ic.index_column_id,
 ColumnName = col.name,
 SchemaName = s.name
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
INNER JOIN
 ?.sys.schemas s ON t.schema_id = s.schema_id
ORDER BY 
 t.name, ind.name, ind.index_id, ic.index_column_id'
 ,@print_command_only = 0

无过程和无光标方法

感谢Sean Lange提醒我如何使用变量连接的技巧来不使用游标或过程，并完成相同的任务。

declare @sql varchar(max) = ''

select @sql = @sql 
        + 'use ' 
        + quotename(name) 
        + char(13) 
        + 'select DBNAME = ''' 
        + name 
        + ''',  
                TableName = t.name,
                IndexName = ind.name,
                IndexId = ind.index_id,
                ColumnId = ic.index_column_id,
                ColumnName = col.name,
                SchemaName = s.name
            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
            INNER JOIN
                sys.schemas s ON t.schema_id = s.schema_id; '  
        + char(13) 
        from sys.databases
        where state = 0
exec(@sql)

推荐阅读