Tag Archives: Generate scripts

How to script out indexes for a database using DMO

Posted on by

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.


Advertisement