生成SQL Server数据库中所有索引、键的脚本

I am looking to generate scripts like:

我希望生成以下脚本:

ALTER TABLE dbo.Person 
    ADD CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (PersonID);
    CREATE INDEX IX_EVT_EVENTS on EVT_EVENTS(prg_event_t1, prg_event_t2, num_order)

The main problem I cannot find all information that I need, for example this query:

我找不到我需要的所有信息的主要问题，例如这个查询:

SELECT t.name TableName, I.* 
FROM sys.indexes I 
Inner Join sys.tables T on i.object_id=t.object_id

returns information about all keys and indexes, but.. there is no "Columns' Name" where can I get them?

返回关于所有键和索引的信息，但是。没有“列的名字”我在哪里可以得到它们?

I tried sys.all_columns and sys.key_constraints also.

我试着系统。all_columns和系统。key_constraints也。

2 个解决方案

#1

Column names are stored in the sys.columns andsys.index_columns tables.

列名存储在sys中。列andsys。index_columns表。

This will select the column names from all tables:

这将从所有表中选择列名:

SELECT  sc.name
FROM    sys.tables st
        INNER JOIN sys.columns sc ON sc.OBJECT_ID = st.OBJECT_ID
WHERE   st.OBJECT_ID = 'ObjectID'

Or to join with the sys.indexes table, this will select the table name and the columns:

或者加入系统。索引表，这将选择表名和列:

SELECT  st.name ,
        sc.name
FROM    sys.indexes si
        INNER JOIN sys.tables st ON si.object_id = st.object_id
        INNER JOIN sys.columns sc ON sc.OBJECT_ID = st.OBJECT_ID

You can add a WHERE clause into the statement to filter on your index column

您可以在语句中添加WHERE子句来过滤索引列

SELECT  si.name ,
        st.name ,
        sc.name
FROM    sys.indexes si
        INNER JOIN sys.tables st ON si.object_id = st.object_id
        INNER JOIN sys.columns sc ON sc.OBJECT_ID = st.OBJECT_ID
WHERE   si.name = 'IX_EVT_EVENTS'

#2

I have tried many scripts in web. All of them had flaws. I finally wrote my script using a post in msdn site and changed it to work on many kind of situations.

我在网上试过很多脚本。他们都有缺点。我最终在msdn网站上写了一篇文章，并将其修改为适用于多种情况。

Note: This scripts works on SQL Server 2012 and later

注意:这个脚本可以在SQL Server 2012和以后的版本中使用

WITH IndexInfo AS (
SELECT ix.object_id
    , ix.NAME AS IndexName
    , ix.type_desc
    , ix.filter_definition
    , ix.is_unique
    , ix.is_primary_key
    , ix.allow_row_locks
    , ix.allow_page_locks
    , ds.NAME AS DataSpaceName
    , ds.type AS DataSpaceType
    , ix.is_padded
    , object_schema_name(ix.object_id) AS SchemaName
    , object_name(ix.object_id) AS TableName
    , IIF(ix.type <= 2, is_included_column, 0) AS HasIncludedColumn
    , IIF(ix.type in (5, 6), 1, 0) AS IsColumnStore
    , (
        SELECT KeyColumns
        FROM (
            SELECT IC2.object_id
                , IC2.index_id
                , STUFF((
                        SELECT ' , ' + C.NAME + IIF(MAX(CONVERT(INT, IC1.is_descending_key)) = 1 AND ix.type <= 2, ' DESC ', ' ')
                        FROM sys.index_columns IC1
                        JOIN sys.columns C ON C.object_id = IC1.object_id
                            AND C.column_id = IC1.column_id
                            --AND IC1.is_included_column = 0   
                            AND IIF(ix.type <= 2, IC1.is_included_column, 0) = 0
                            AND IIF(ix.type <= 2, IC1.key_ordinal, ic.index_column_id) > 0
                        WHERE IC1.object_id = IC2.object_id
                            AND IC1.index_id = IC2.index_id
                        GROUP BY IC1.object_id
                            , C.NAME
                            , index_id
                            , IC1.key_ordinal
                            , IC1.index_column_id
                        ORDER BY IIF(ix.type <= 2, IC1.key_ordinal, IC1.index_column_id)
                        FOR XML PATH('')
                        ), 1, 2, '') KeyColumns
            FROM sys.index_columns IC2
            GROUP BY IC2.object_id
                , IC2.index_id
            ) tmp
        WHERE tmp.object_id = ix.object_id
            AND tmp.index_id = ix.index_id
        ) AS KeyColumnsStr
    , (
        SELECT IncludedColumns
        FROM (
            SELECT IC2.object_id, IC2.index_id
                , STUFF((
                        SELECT ' , ' + C.NAME
                        FROM sys.index_columns IC1
                        JOIN sys.columns C ON C.object_id = IC1.object_id
                            AND C.column_id = IC1.column_id
                            --AND IC1.is_included_column = 1   
                            AND IIF(ix.type <= 2, IC1.is_included_column, 0) <> 0
                        WHERE IC1.object_id = IC2.object_id
                            AND IC1.index_id = IC2.index_id
                        --and IIF(ix.type <= 2, IC1.key_ordinal, ic.index_column_id) > 0
                        GROUP BY IC1.object_id, C.NAME, index_id, IC1.key_ordinal, IC1.index_column_id
                        ORDER BY IIF(ix.type <= 2, IC1.key_ordinal, IC1.index_column_id)
                        FOR XML PATH('')
                        ), 1, 2, '') IncludedColumns
            FROM sys.index_columns IC2
            GROUP BY IC2.object_id, IC2.index_id
            ) tmp
        WHERE tmp.object_id = ix.object_id
            AND tmp.index_id = ix.index_id
            AND IncludedColumns IS NOT NULL
        ) AS IncludedColumnsStr
FROM sys.indexes ix
INNER JOIN sys.index_columns ic ON ic.index_id = ix.index_id AND ic.object_id = ix.object_id
INNER JOIN sys.columns col ON col.column_id = ic.column_id AND col.object_id = ix.object_id
INNER JOIN sys.data_spaces ds ON ix.data_space_id = ds.data_space_id
WHERE ix.NAME IS NOT NULL AND IIF(ix.type <= 2, ic.key_ordinal, ic.index_column_id) > 0
)
, Scrpt AS (
SELECT *
    , 'CREATE ' 
    + CASE WHEN is_unique = 1 THEN ' UNIQUE ' ELSE '' END 
    + type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' + IndexName + ' ON ' 
        + SchemaName + '.' + TableName + '(' + KeyColumnsStr + ')' 
    + ISNULL(IIF(HasIncludedColumn > 0, ' ', ' INCLUDE (' + IncludedColumnsStr + ')'), '') 
    + IIF(filter_definition IS NULL, ' ', ' WHERE ' + filter_definition) 
    + ' WITH (' 
        + CASE WHEN IsColumnStore = 1 THEN ' DROP_EXISTING = OFF '
          ELSE
                IIF(is_padded = 1, ' PAD_INDEX = ON ', ' PAD_INDEX = OFF ') + ',' 
                + ' DROP_EXISTING = OFF ' + ',' 
                + ' ONLINE = OFF ' + ',' 
                + IIF(allow_row_locks = 1, ' ALLOW_ROW_LOCKS = ON ', ' ALLOW_ROW_LOCKS = OFF ') + ',' 
                + IIF(allow_page_locks = 1, ' ALLOW_PAGE_LOCKS = ON ', ' ALLOW_PAGE_LOCKS = OFF ') 
          END
        + ' ) ' 
    + IIF(DataSpaceType = 'FG','ON [' + DataSpaceName + ']', '') AS CreateIndexScript
FROM IndexInfo
)
SELECT DISTINCT IndexName, CreateIndexScript
FROM Scrpt
WHERE object_id = object_id('dbo.SWPartition')

As a credit to original author, I mentions the original link here How to Generate Index Creation Scripts

作为对原始作者的赞扬，我在这里提到了如何生成索引创建脚本的原始链接