Why do you need this? One of the drawbacks of the “Generate Scripts” option in SQL Server Management Studio is that SSMS scripts out the Primary Key constraints along with the CREATE TABLE script. So, if the table is already created, the primary key will not be created if you use the script that was generated by the SSMS Generate Scripts Wizard.
The steps that you need to follow are for generating the primary key constraints or clustered indexes for user defined tables:
1. You will have to create the Stored Procedure from my previous blog post on the source database.
2. Then enable the OLE AUTOMATION and XP_CMDSHELL on the server.
EXEC sp_configure 'Show Advanced',1
reconfigure with override
go
EXEC sp_configure 'Ole Automation',1
reconfigure with override
go
EXEC sp_configure 'xp_cmdshell',1
reconfigure with override
go
3. Use the following script to generate the list of Primary Keys for user defined tables in a database:
Script for generating primary key creation script:
set nocount on
print 'DECLARE @return_value int '
select '
EXEC @return_value = [dbo].[proc_genscript]
@ServerName = ''SQLSERVER NAME'', /*Replace this with the SQL server name*/
@DBName = ''Northwind'', /*Replace this with the database name*/
@ObjectName = '''+[name]+''',
@ObjectType = ''KEY'',
@TableName = ''' +object_name(parent_object_id)+''',
@ScriptFile = ''C:\Database\'+[name]+'.sql'' /* Replace this with the directory path where you want the create scripts to be saved */
SELECT ''Return Value '' = @return_value'+char(13)
from sys.objects
where type = 'PK'
and parent_object_id in (select id from sys.sysobjects where xtype = 'U')
Script for generating clustered indexes creation script:
set nocount on
print 'DECLARE @return_value int '
select '
EXEC @return_value = [dbo].[proc_genscript]
@ServerName = ''SQLSERVER NAME'', /*Replace this with the SQL server name*/
@DBName = ''Northwind'', /*Replace this with the database name*/
@ObjectName = '''+[name]+''',
@ObjectType = ''INDEX'', @TableName = ''' +object_name(id)+''',
@ScriptFile = ''C:\Database\'+[name]+'.sql'' /* Replace this with the directory path where you want the create scripts to be saved */
SELECT ''Return Value '' = @return_value'+char(13)
from sys.objects
where type = 'PK'
and parent_object_id in (select id from sys.sysobjects where xtype = 'U')
4. Then use the scripts obtained from the above output to generate the Primary Key creation scripts in the folder that you mentioned. In the above example, the *.sql scripts would get created in the C:\Database folder.
5. Then use the following script to generate the SQLCMD commands for running those *.sql scripts against the source database.
create table #filenames (file_name varchar(1000))
insert into #filenames
exec xp_cmdshell 'dir <folder path>\*.sql /b'
select 'sqlcmd -S <servername> -d <databasename> -E -i ' +file_name
from #filenames
where file_name like '%sql%'
drop table #filenames
where
<server name> = SQL Server instance name
<database name> = Database Name on which you want the objects to be created
<folder path> = the folder where you want the .sql file to be created. This has to be the same folder path where you saved the .sql files in Step 3.
6. Now put the commands obtained in Step 5 into a .bat file saved at the same location where the .sql files were created in Step 5. Run the .BAT file from a command prompt window and all the script files will get executed against the context of the database that you provided.
The above set of steps loops through the sysindexes system catalog and picks up all non-fulltext index and generates the CREATE INDEX scripts for all the user database tables using DMO.
**** Before running this on a production database, please test out the above solution on a test database
TroubleshootingSQL 