生成SQL Server数据库中所有索引，键的脚本 [英] Generate script of all indexes, keys in the SQL Server database

问题描述

我希望生成以下脚本：

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?

我尝试了sys.all_columns和sys.key_constraints还有。

I tried sys.all_columns and sys.key_constraints also.

推荐答案

列名存储在 sys.columns 和 sys.index_columns 表格。

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

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

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'

或者要加入 sys.indexes 表，这将选择表名和列：

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

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

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

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'

这篇关于生成SQL Server数据库中所有索引，键的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！