Browse DevX
Sign up for e-mail newsletters from DevX


Scripting SQL Server 2000 Indexes : Page 5

SQL Server 2000's Enterprise Manager contains a scripting tool that you can use to extract basic index drop and create commands. However, to create such a script you'll have to painstakingly cut and paste, and the result will not have any error checking. In this article you'll see how to construct SQL Server stored procedures that will generate index scripts more easily and flexibly.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Scripting an Index
To generate the actual index script, usp_ScriptIndexes calls usp_ScriptOneIndex and calls it for each index that it wants to have scripted. This latter stored procedure also uses the temporary table #TempSysindexes to pick up specific information about each index so that the scripted output will be appropriate. Listing 3 shows usp_GenerateOneIndex.

The best way to understand how usp_GenerateOneIndex works is to see its output, in this case for the same authors table that you saw before. Listing 4 shows the output for the authors table, including one statistics entry.

Now you have a test for each action, and the script will not try to create or drop an index or constraint unless it really exists.

Foreign Key References
In the case of the primary key constraint UPKCL_auidind, the generated Transact-SQL will first check to see whether any foreign keys reference this table, and if so, print out a message that the foreign key must be dropped first. For instance, the publishers table's primary key has a foreign key referencing it, and the generated script will send you the message:

Could not drop index UPKCL_pubind for table dbo.[publishers] because it is referenced by at least one foreign key constraint. Drop the foreign key constraint(s) first.

The test to do this is based on finding the primary key (or unique constraint) name in the INFORMATION_SCHEMA view called REFERENTIAL_CONSTRAINTS. Using the view allows you to bypass accessing any system tables, and insulates the code from changes to system tables down the road.

Is it an Index?
You need some way to determine whether a given name is really an index or not, and the IndexProperty() function will do the job. Every index will either be clustered or non-clustered, and if the name is not an index for the table in question, the result will be null. Consequently, you can just test for whether it's not null:

And IndexProperty(Object_Id('dbo.[authors]'), 'UPKCL_auidind', 'IsClustered') Is Not Null

This applies to constraints as well as indexes.

Fillfactor, Nocheck, and Filegroup
Notice that usp_ScriptOneIndex has code in it detecting whether the Fillfactor is greater than zero, whether the CnstIsNotTrusted property is 1 from the temporary table, and what the Filegroup name is, and fills them in accordingly. So the primary key create command for the authors table also uses the Filegroup option:

Alter Table Pubscopy2.dbo.authors Add Constraint UPKCL_auidind Primary Key Clustered ([au_id]) On [PRIMARY]

I use the left and right square brackets to allow spaces in table and column names (see the Sidebar, Delimiting Strings).

There you have it! You can now create procedures that will generate Transact-SQL scripts to drop and create indexes in an orderly manner, with existence and foreign key testing embedded. And you can modify the driver procedure to customize it for your needs. Happy computing!

Ron Talmage heads Prospice, LLC, a database consulting firm based in Seattle, and is the author of "Microsoft SQL Server 7.0 Administrator's Guide" (Prima Publishing). He is a SQL Server MVP, PASS newsletter co-editor, and current president of the Pacific Northwest SQL Server Users Group, and also writes for SQL Server Professional and SQL Server Magazine. You can reach Ron by email at rtalmage@prospice.com.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date