How to script out indexes for a database using DMO


Create the following stored procedure in your database: 

CREATE PROCEDURE [dbo].[proc_genscript]
 

@ServerName varchar(30),
 

@DBName varchar(30),
 

@ObjectName varchar(50),
 

@ObjectType varchar(10),
 

@TableName varchar(50),
 

@ScriptFile varchar(255)
 

AS
 

DECLARE @CmdStr varchar(255)
 

DECLARE @object int
 

DECLARE @hr int
 

SET NOCOUNT ON
 

SET @CmdStr = 'Connect('+@ServerName+')'
 

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
 

EXEC @hr = sp_OASetProperty @object, 'LoginSecure', TRUE
 

EXEC @hr = sp_OAMethod @object,@CmdStr
 

SET @CmdStr =
 

CASE @ObjectType
 

WHEN 'Database' THEN 'Databases("'
 

WHEN 'Procedure' THEN 'Databases("' + @DBName + '").StoredProcedures("'
 

WHEN 'View' THEN 'Databases("' + @DBName + '").Views("'
 

WHEN 'Table' THEN 'Databases("' + @DBName + '").Tables("'
 

WHEN 'Index' THEN 'Databases("' + @DBName + '").Tables("' +
 

@TableName + '").Indexes("'
 

WHEN 'Trigger' THEN 'Databases("' + @DBName + '").Tables("' +
 

@TableName + '").Triggers("'
 

WHEN 'Key' THEN 'Databases("' + @DBName + '").Tables("' + @TableName +
 

'").Keys("'
 

WHEN 'Check' THEN 'Databases("' + @DBName + '").Tables("' +
 

@TableName + '").Checks("'
 

WHEN 'Job' THEN 'Jobserver.Jobs("'
 

END
 

SET @CmdStr = @CmdStr + @ObjectName + '").Script(5,"' + @ScriptFile + '")'
 

EXEC @hr = sp_OAMethod @object, @CmdStr
 

EXEC @hr = sp_OADestroy @object


After the above stored procedure is created, you can create the commands for generating the index creation scripts. 

The script below will generate the CREATE script for the Primary Key for the table tbl_tran_repl (Key name = PK_tbl_tran_repl) and store it in the file PK_tbl_tran_repl.sql under the C:\Database folder. 

Example: 

DECLARE @return_value int
 

EXEC @return_value = [dbo].[proc_genscript]
 

@ServerName = '<SQL Server Name>',
 

@DBName = '<Database Name>',
 

@ObjectName = 'PK_tbl_tran_repl',
 

@ObjectType = 'KEY',
 

@TableName = 'tbl_tran_repl',
 

@ScriptFile = 'C:\Database\PK_tbl_tran_repl.sql'
 

SELECT 'Return Value ' = @return_value


The original script is obtained from Database Journal.

Advertisements

2 thoughts on “How to script out indexes for a database using DMO

  1. Pingback: Steps to script out the primary key constraints or clustered indexes in a database using DMO « Troubleshootingsql's Blog

  2. SELECT ‘ CREATE ‘ +
    CASE
    WHEN I.is_unique = 1 THEN ‘ UNIQUE ‘
    ELSE ”
    END +
    I.type_desc COLLATE DATABASE_DEFAULT + ‘ INDEX ‘ +
    I.name + ‘ ON ‘ +
    SCHEMA_NAME(T.schema_id) + ‘.’ + T.name + ‘ ( ‘ +
    KeyColumns + ‘ ) ‘ +
    ISNULL(‘ INCLUDE (‘ + IncludedColumns + ‘ ) ‘, ”) +
    ISNULL(‘ WHERE ‘ + I.filter_definition, ”) + ‘ WITH ( ‘ +
    CASE
    WHEN I.is_padded = 1 THEN ‘ PAD_INDEX = ON ‘
    ELSE ‘ PAD_INDEX = OFF ‘
    END + ‘,’ +
    ‘FILLFACTOR = ‘ + CONVERT(
    CHAR(5),
    CASE
    WHEN I.fill_factor = 0 THEN 100
    ELSE I.fill_factor
    END
    ) + ‘,’ +
    — default value
    ‘SORT_IN_TEMPDB = OFF ‘ + ‘,’ +
    CASE
    WHEN I.ignore_dup_key = 1 THEN ‘ IGNORE_DUP_KEY = ON ‘
    ELSE ‘ IGNORE_DUP_KEY = OFF ‘
    END + ‘,’ +
    CASE
    WHEN ST.no_recompute = 0 THEN ‘ STATISTICS_NORECOMPUTE = OFF ‘
    ELSE ‘ STATISTICS_NORECOMPUTE = ON ‘
    END + ‘,’ +
    ‘ ONLINE = OFF ‘ + ‘,’ +
    CASE
    WHEN I.allow_row_locks = 1 THEN ‘ ALLOW_ROW_LOCKS = ON ‘
    ELSE ‘ ALLOW_ROW_LOCKS = OFF ‘
    END + ‘,’ +
    CASE
    WHEN I.allow_page_locks = 1 THEN ‘ ALLOW_PAGE_LOCKS = ON ‘
    ELSE ‘ ALLOW_PAGE_LOCKS = OFF ‘
    END + ‘ ) ON [‘ +
    DS.name + ‘ ] ‘ + CHAR(13) + CHAR(10) + ‘ GO’ [CreateIndexScript]
    FROM sys.indexes I
    JOIN sys.tables T
    ON T.object_id = I.object_id
    JOIN sys.sysindexes SI
    ON I.object_id = SI.id
    AND I.index_id = SI.indid
    JOIN (
    SELECT *
    FROM (
    SELECT IC2.object_id,
    IC2.index_id,
    STUFF(
    (
    SELECT ‘ , ‘ + C.name + CASE
    WHEN MAX(CONVERT(INT, IC1.is_descending_key))
    = 1 THEN
    ‘ DESC ‘
    ELSE
    ‘ ASC ‘
    END
    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
    WHERE IC1.object_id = IC2.object_id
    AND IC1.index_id = IC2.index_id
    GROUP BY
    IC1.object_id,
    C.name,
    index_id
    ORDER BY
    MAX(IC1.key_ordinal)
    FOR XML PATH(”)
    ),
    1,
    2,

    ) KeyColumns
    FROM sys.index_columns IC2
    –WHERE IC2.Object_id = object_id(‘Person.Address’) –Comment for all tables
    GROUP BY
    IC2.object_id,
    IC2.index_id
    ) tmp3
    )tmp4
    ON I.object_id = tmp4.object_id
    AND I.Index_id = tmp4.index_id
    JOIN sys.stats ST
    ON ST.object_id = I.object_id
    AND ST.stats_id = I.index_id
    JOIN sys.data_spaces DS
    ON I.data_space_id = DS.data_space_id
    JOIN sys.filegroups FG
    ON I.data_space_id = FG.data_space_id
    LEFT JOIN (
    SELECT *
    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
    WHERE IC1.object_id = IC2.object_id
    AND IC1.index_id = IC2.index_id
    GROUP BY
    IC1.object_id,
    C.name,
    index_id
    FOR XML PATH(”)
    ),
    1,
    2,

    ) IncludedColumns
    FROM sys.index_columns IC2
    –WHERE IC2.Object_id = object_id(‘Person.Address’) –Comment for all tables
    GROUP BY
    IC2.object_id,
    IC2.index_id
    ) tmp1
    WHERE IncludedColumns IS NOT NULL
    ) tmp2
    ON tmp2.object_id = I.object_id
    AND tmp2.index_id = I.index_id
    WHERE I.is_primary_key = 0
    AND I.is_unique_constraint = 0
    –AND I.Object_id = object_id(‘Person.Address’) –Comment for all tables
    –AND I.name = ‘IX_Address_PostalCode’ –comment for all indexes

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s