Steps to script out the primary key constraints or clustered indexes in a database using DMO


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

Advertisements

How to find out how many objects of different types are there in a SQL database


I have sometimes found the need on data corruption cases to compare the number of objects exported to the destination database with the source database to find out which objects got exported and which didn’t. You would need to run this script against the source and destination database and compare the output. 

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

Script 

select 

CASE xtype 

WHEN 'C' THEN 'CHECK constraint' 

WHEN 'D' THEN 'Default or DEFAULT constraint ' 

WHEN 'F' THEN 'FOREIGN KEY constraint ' 

WHEN 'L' THEN 'Log ' 

WHEN 'FN' THEN 'Scalar function ' 

WHEN 'IF' THEN 'In-lined table-function ' 

WHEN 'P' THEN 'Stored procedure ' 

WHEN 'PK' THEN 'PRIMARY KEY constraint (type is K) ' 

WHEN 'RF' THEN 'Replication filter stored procedure' 

WHEN 'S' THEN 'System table ' 

WHEN 'TF' THEN 'Table function ' 

WHEN 'TR' THEN 'Trigger ' 

WHEN 'U' THEN 'User table ' 

WHEN 'UQ' THEN 'UNIQUE constraint (type is K) ' 

WHEN 'V' THEN 'View ' 

WHEN 'X' THEN 'Extended stored procedure' 

ELSE 'UNKNOWN' 

END, count(*) as counts 

from sys.sysobjects 

group by CASE xtype 

WHEN 'C' THEN 'CHECK constraint' 

WHEN 'D' THEN 'Default or DEFAULT constraint ' 

WHEN 'F' THEN 'FOREIGN KEY constraint ' 

WHEN 'L' THEN 'Log ' 

WHEN 'FN' THEN 'Scalar function ' 

WHEN 'IF' THEN 'In-lined table-function ' 

WHEN 'P' THEN 'Stored procedure ' 

WHEN 'PK' THEN 'PRIMARY KEY constraint (type is K) ' 

WHEN 'RF' THEN 'Replication filter stored procedure' 

WHEN 'S' THEN 'System table ' 

WHEN 'TF' THEN 'Table function ' 

WHEN 'TR' THEN 'Trigger ' 

WHEN 'U' THEN 'User table ' 

WHEN 'UQ' THEN 'UNIQUE constraint (type is K) ' 

WHEN 'V' THEN 'View ' 

WHEN 'X' THEN 'Extended stored procedure' 

ELSE 'UNKNOWN' 

END

[Blog Reference]: Listing SQL instances in your environment


I have had multiple questions on how to enumerate SQL instances in your complete environment to run maintenance operations from a centralized environment. There are multiple tools which implement one of the methods or a variation of the methods described in the blog post below.

I keep referring people to this blog post. So, I thought I would put a referral of the same on my blog so that I can find it easily the next time.

Jonathan Sayce [Blog] has shown in his blog post what are the different methods to achieve this.

http://sqlblogcasts.com/blogs/jonsayce/archive/2008/02/10/programatically-listing-sql-servers.aspx