Browse DevX
Sign up for e-mail newsletters from DevX


Scripting SQL Server 2000 Indexes : Page 2

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

Enterprise Manager Scripting
Figure 1: You can use the Preview button to see the script that Enterprise Manager will generate.
By default, it's best to use the built-in tools that SQL Server provides rather than coding your own. You can use Enterprise Manager's scripting tool to generate table and stored procedure scripts, and the results are very satisfactory. However, when you try to capture just indexes the results have some limits. To create an Enterprise Manager script you can first select a table in Enterprise Manager, right-click, choose the All Tasks option, and then choose Generate SQL Script. This brings up the scripting dialog. You can navigate the tabs to remove the table's Create and Drop options and add the Indexes. You'll get a table's indexes that are not also primary key or unique constraints, as shown in Figure 1.

Figure 2: When you choose constraints in addition to indexes, Enterprise Manager now gives additional information about defaults and check constraints; information that is unrelated to indexes.

Missing the primary key and uniqueness constraints is a real problem. So what happens if you add in the constraints? The only option that Enterprise Manager gives is "Script PRIMARY keys, FOREIGN keys, defaults, and check constraints." Unfortunately, checking this option gives back too much data, as shown in Figure 2.

If all you want to do is capture the indexes for one table, cutting and pasting the information from Enterprise Manager scripting may be enough. However, consider the following index script, generated for the authors table, with default and check constraint information removed:

ALTER TABLE [dbo].[authors] WITH NOCHECK ADD CONSTRAINT [UPKCL_auidind] PRIMARY KEY CLUSTERED ( [au_id] ) ON [PRIMARY] GO CREATE INDEX [aunmind] ON [dbo].[authors]([au_lname], [au_fname]) ON [PRIMARY] GO

First of all, you're missing any DROP commands for the constraint and the index so you'll have to add them in manually if you need them. Second, there's no testing. That is, the script should test whether the index exists before trying to add it, otherwise you'll get an error. What you really need is something that drops the old constraint or index if it's there, then adds the new one. Here's what would be better, following the model that Enterprise Manager uses for a table, in pseudo-code:

If the constraint or index exists Drop the constraint or index Go If the constraint or index does not exist Create the constraint or index Go

Also, you can't predict what order the indexes will come out in. For large tables it's wise to build the clustered index first and then all indexes later. Secondary indexes use the clustered key in their leaf nodes. You might even want the flexibility of clustered keys only, which Enterprise Manager scripting does not have.

Another potential problem arises when you try to drop primary key constraints that have foreign key references to them—you must first drop the primary key. Enterprise Manager scripting completely ignores this issue.

Even this is not enough. What about statistics? SQL Server keeps statistics on tables that can greatly aid query optimization for large tables, but Enterprise Manager will not script them. If you're moving large amounts of data, though, you might want to recreate those statistics on a target database after you load your data.

In the code developed in this article you'll see how you can use Transact-SQL custom stored procedures to generate index scripts in a safer and more flexible fashion—one that does not require cutting and pasting, and that has tests embedded in the code.

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