如何为具有标识插入列的表作为通用脚本重新创建索引？ [英] How to re create indexes for tables with identity insert columns as a generic script?

问题描述

如何使用Identity为Sql中的数据库插入列重新创建表的索引？

我想使用通用脚本执行相同操作。



目前我正在执行查询并执行输出。不是通用脚本。



查询如下：

How to re create indexes for tables with Identity Insert columns for a Database in Sql?
I want to do the same using a generic script.

Currently I am executing the query and executing the output. Not as a generic script.

Query as below:

SELECT  
'CREATE INDEX ' +  i.name + ' ON ' + 
OBJECT_NAME(ic.OBJECT_ID)+ 
'('+ COL_NAME(ic.OBJECT_ID,ic.column_id) +')'
--, i.name AS IndexName,
--, OBJECT_NAME(ic.OBJECT_ID) AS TableName,
--, COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM    
sys.indexes AS i INNER JOIN 
sys.index_columns AS ic ON  
i.OBJECT_ID = ic.OBJECT_ID
AND 
i.index_id = ic.index_id
inner JOIN 
sys.tables t 
ON 
t.object_id =i.object_id
WHERE   
i.index_id >= 1 
---- & For fetching tables with Identity Insert & Primary Key 
and i.is_primary_key = 1
& For fetching tables with Identity Insert ON
and  EXISTS 
(
SELECT * FROM sys.identity_columns
WHERE [object_id] = t.[object_id]
)
	ORDER BY t.name, i.name;

我尝试过：



尝试查询如下：

查询如下：

What I have tried:

Tried Query is as below:
Query as below:

SELECT  
'CREATE INDEX ' +  i.name + ' ON ' + 
OBJECT_NAME(ic.OBJECT_ID)+ 
'('+ COL_NAME(ic.OBJECT_ID,ic.column_id) +')'
--, i.name AS IndexName,
--, OBJECT_NAME(ic.OBJECT_ID) AS TableName,
--, COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM    
sys.indexes AS i INNER JOIN 
sys.index_columns AS ic ON  
i.OBJECT_ID = ic.OBJECT_ID
AND 
i.index_id = ic.index_id
inner JOIN 
sys.tables t 
ON 
t.object_id =i.object_id
WHERE   
i.index_id >= 1 
---- & For fetching tables with Identity Insert & Primary Key 
and i.is_primary_key = 1
& For fetching tables with Identity Insert ON
and  EXISTS 
(
SELECT * FROM sys.identity_columns
WHERE [object_id] = t.[object_id]
)
	ORDER BY t.name, i.name;

推荐答案

这应该让你开始我认为，代码在C＃中，但你可以在SQL中使用查询部分：

This should get you started I think, the code is in C# but you can use the query part in SQL if you wish:

var tableName = "MyTable";
var schema = "dbo";
var command = msConnection.CreateCommand();

var msSql = string.Format(@"SELECT col.name, is_rowguidcol, is_identity
                            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   t.is_ms_shipped = 0
                                    AND (col.is_rowguidcol > 0 OR col.is_identity > 0)
                                    AND OBJECT_SCHEMA_NAME(ind.object_id) = '{0}'
                                    AND OBJECT_NAME(ind.object_id) = '{1}'
                            ", schema, tableName);

command.CommandText = msSql;

using (var dr = command.ExecuteReader(CommandBehavior.SequentialAccess))
{
    while (dr.Read())
    {
        // 0 = ColumnName, 1 = Rowguid, 2 = Identity.
        object[] rowObjects = new object[3];
        dr.GetValues(rowObjects);
        var columnName = rowObjects[0].ToString().ToLower();
        var isrowguidcol = rowObjects[1].ToString();
        var isidentity = rowObjects[2].ToString();

        if (!string.IsNullOrEmpty(columnName))
        {
            if (isrowguidcol == "True")
            {
                // ROWGUIDCOL found for this table.
                Debug.Print("ROWGUIDCOL  " + tableName + " " + columnName);
            }
            else if (isidentity == "True")
            {
                // IDENTITY found for this table.
                Debug.Print("IDENTITY  " + tableName + " " + columnName);
            }
        }
    }
}

另一种选择是使用这个 SMO scripter ，它是Robert Kanasz SMO脚本编写器的更新版本，支持 IDENTITY ：

n-Scripting的SMO教程3 [ ^ ]

请务必取消选中所有选项除了键，索引，约束，然后按脚本按钮。

Another option is to use this "SMO scripter", it is an updated version of Robert Kanasz SMO scripter that supports IDENTITY:
SMO Tutorial 3 of n - Scripting[^]
Be sure to uncheck all options except "keys, indexes, constraints" before you press the "Script" button.

这篇关于如何为具有标识插入列的表作为通用脚本重新创建索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！