Browse DevX
Sign up for e-mail newsletters from DevX


Scripting SQL Server 2000 Indexes : Page 4

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 Key: Getting Index Information
Getting complete and accurate information about all the indexes in a database is the key to scripting indexes. Once you have the index information, generating the index create and drop commands is relatively straightforward. Getting that information is the job of usp_GetIndexInfo, which fills up a temporary table called #TempSysindexes. Here's the table definition:

Create Table dbo.#TempSysindexes ( DbName nvarchar(128) , TableID int , TableName nvarchar(128) , TableOwner nvarchar(128) , IndexName nvarchar(128) , IndexID int , IndexKeyString nvarchar(2048) , IsPrimaryKey tinyint , CnstIsClustKey tinyint , IsUniqueConstraint tinyint , IndexFillFactor tinyint , IsClustered tinyint , IsStatistics tinyint , IsAutoStatistics tinyint , IsHypothetical tinyint , IsPadIndex tinyint , IsUniqueIndex tinyint , IsPageLockDisallowed tinyint , IsRowLockDisallowed tinyint , CnstIsNonclustKey tinyint , CnstIsNotTrusted tinyint , FileGroupID int , FileGroupName nvarchar(128) )

There's a lot of information here! The index-specific information is mostly picked up using the INDEXPROPERTY() function, which you can use to determine whether the index is a primary key, whether it's clustered, or unique, what its fill factor is, whether it's actually statistics and not an index, and so on. What the INDEXPROPERTY() function cannot deliver is what column names are in the index key string, and what the filegroup and filegroup name for the index is. In any case, Listing 1 shows usp_GetIndexInfo, and how it does its work.

A dynamic SQL String does the bulk of the work because the Transact-SQL ObjectProperty() and IndexProperty() system functions must be executed in the context of the database with the tables you're trying to use them on. The first command of the dynamic string Uses the database, and even though you execute the procedure in the Utilities database, the Select command executes in the database you send as a parameter.

Once you load the index information, the procedure loops through each table id and then for each index belonging to that table id, loads the index key column and filegroup information. It was easy just to add that code to the dynamic string and execute the loop in the context of the target database.

Getting the Index Columns
Every index has a set of one or more columns from its table, and it stores the values of those columns in the actual index, in sorted order, for lookup purposes. There is no system function or system table that provides this information so you'll call another procedure that will get it, usp_GetIndexColumns, which you can see in Listing 2.

usp_GetIndexColumns returns a comma-delimited string of the index key columns in a format that you can use to generate a create script for a constraint or index. In addition, SQL Server 2000 allows descending as well as ascending indexes, and this procedure captures that as well. In order to get that string it loads all the columns that belong to an index into a temporary table, in their proper order, along with whether the column has the Descending property. Then the procedure loops through the temporary table, concatenating the values to create the string. It loads that column data by joining on the sysobjects, syscolumns, sysindexes, and sysindexkeys tables, much in the same fashion that the SQL Server system stored procedure sp_helpindex does (which was the inspiration for this procedure—but unfortunately sp_helpindex could not be used to just get the column string).

Now that you've seen how to load the index information, it's time to move back out to the outermost driver procedure and see how to generate the index script.

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