指定したテーブルのインデックス名を取得する方法 SQL Server

2379 ワード
Transact-SQL SQLServer SQLServer テキストリンク

環境

SQL Server 2012

試し

get_indexname.sql
SELECT   S.name AS SchemaName,
         O.name AS ObjectName,
         I.name AS IndexName
FROM     sys.objects AS O
             INNER JOIN sys.schemas AS S
                ON O.schema_id = S.schema_id
             INNER JOIN sys.indexes AS I
                ON O.object_id = I.object_id
WHERE    I.is_disabled = 0
ORDER BY ObjectName

ストアドプロシージャを実装する

CREATE_sp_GET_INDEX_NAME.sql
CREATE PROCEDURE sp_GET_INDEX_NAME
  @schema_name VARCHAR(MAX),
  @table_name VARCHAR(MAX)
AS
BEGIN
  DECLARE [cur_index_name] CURSOR FOR
  SELECT   I.name AS IndexName
  FROM     sys.objects AS O
               INNER JOIN sys.schemas AS S
                  ON O.schema_id = S.schema_id
               INNER JOIN sys.indexes AS I
                  ON O.object_id = I.object_id
  WHERE    S.NAME = @schema_name
           AND O.name = @table_name
           AND I.is_disabled = 0;

  OPEN [cur_index_name];

  DECLARE @index_name VARCHAR(MAX);

  FETCH NEXT FROM [cur_index_name] INTO
    @index_name;

  WHILE (@@FETCH_STATUS = 0)
  BEGIN
    PRINT 'INDEX[' + @index_name + ']';
    FETCH NEXT FROM [cur_index_name] INTO
      @index_name;
  END

  CLOSE [cur_index_name]
  DEALLOCATE [cur_index_name]
END

ストアドプロシージャを実行する

DECLARE @schema_name VARCHAR(MAX);
SET @schema_name = 'dbo';

DECLARE @table_name VARCHAR(MAX);
SET @table_name = 'ITEM';

EXECUTE sp_GET_INDEX_NAME @schema_name, @table_name;