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.
