Scripting an Index
To generate the actual index script, usp_ScriptIndexes
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
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:
'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!