Click here to Skip to main content
16,005,178 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:
SQL
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:
SQL
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;
Posted
Updated 6-Apr-16 3:14am
Comments
Tomas Takac 6-Apr-16 2:21am    
What's your question? What do you mean by "Not as a generic script"?

This should get you started I think, the code is in C# but you can use the query part in SQL if you wish:
C#
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);
            }
        }
    }
}
 
Share this answer
 
v2
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.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900