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
) ON [PRIMARY]
CREATE INDEX [aunmind]
ON [dbo].[authors]([au_lname], [au_fname])
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
If the constraint or index does not exist
Create the constraint or index
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 themyou 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 fashionone that does not require cutting and pasting, and that has tests embedded in the code.