Browse DevX
Sign up for e-mail newsletters from DevX


Scripting SQL Server 2000 Indexes : Page 3

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

The Scripting Strategy
SQL Server 2000 gives you two major methods for determining indexing information: system functions and system tables. The system functions are OBJECTPROPERTY() and INDEXPROPERTY(), and the system tables are sysobjects, sysindexes, and sysindexkeys. Microsoft recommends using system functions whenever possible and the following code will do that. However, some index information can only be found in system tables so the strategy that follows uses both system tables and system functions. For more information, see the sidebar, Using System Functions Instead of System Tables.

The stored procedures that you'll learn about in this article use the following strategy to script out index information. Here's the strategy in outline form:

Driver procedure: usp_ScriptIndexes Create a temporary table Call usp_GetIndexInfo Fill the temporary table Loop: for each index, Call usp_GetIndexKeys Update temporary table Loop: for each table, Loop: for each index, Call usp_ScriptOneIndex Load options Generate drop script Generate create script

The outermost stored procedure, usp_ScriptIndexes, creates a temporary table to store index information, and then calls usp_GetIndexInfo to populate the temporary table. You have to take this approach because if the called procedure created the temporary table then it would fall out of scope when you return to the driver stored procedure. The usp_GetIndexInfo stored procedure calls a very specialized procedure, usp_GetIndexKeys, to get the index key information: that is to say, what the column names are for the index and whether they are ascending or descending. Once the temp table is populated, usp_ScriptIndexes can loop through the indexes in a flexible manner, table by table, generating the scripts for each index by calling usp_ScriptOneIndex.

Driving the Scripting
The outermost stored procedure, which I'm calling the driver, is usp_ScriptIndexes. Its interface is worth taking a look at before delving into the special tasks that need to be performed prior to its execution. Here's the procedure declaration:

Use Utilities Set Nocount On Go If Object_ID('dbo.usp_ScriptIndexes', 'P') Is Not Null Drop Procedure dbo.usp_ScriptIndexes Go Create Procedure dbo.usp_ScriptIndexes @DbName varchar(40) , @OrderOption varchar(40) , @Create tinyint = 1 , @Drop tinyint = 1 , @IncludeStatistics tinyint = 1 , @Debug tinyint = 0 As

All of these procedures go in a database called "Utilities" that you can change to suit your needs. These procedures are more useful if they can sit in their own database. You can pass the database name that you want to have the indexes scripted. The second parameter is @OrderOption, which you can use to determine whether to generate the indexes with a primary key first, or a clustered key first. This is useful when the primary key and clustered index differ. The third and fourth parameters determine whether to generate create and/or drop commands in the script. The fifth parameter, @IncludeStatistics, lets you decide whether to include statistics in the output. You can modify this procedure to accept additional parameters if you would like more options.

Now that you know what the options are for the output, it's time to dive into getting index information.

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