Scripting SQL Server 2000 Indexes

By Talmage, Ron
Published in: CODE Magazine: 2003 - January/February
Publication Date: January 1, 2003
Last updated: February 20, 2019

Published in:

Filed under:

Adding indexes to tables is the most effective way to optimize your SQL Server query performance. Tables that you query often may require several indexes in order to handle the various search arguments you send.

There is one particular situation where indexes on large tables are a hindrance, though. When you're bulk-copying data into the table, it's best to have either no index at all, or just a clustered index. For those situations it is best to use Transact-SQL scripts to remove and add those indexes. In this article you'll learn how to use Transact-SQL stored procedures to generate Transact-SQL indexing scripts that are more robust and less laborious than SQL Server's Enterprise Manager scripting tool.

Recently I was involved in a project that needed to move a number of large database tables from one server to another. We decided to use the bcp.exe command to export the data, and BULK INSERT to import it into the new database. We did not want to have any indexes, or at a minimum a clustered index on the new tables, in order to optimize the import process.

Initially we used the Enterprise Manager scripting tool but ran into a number of problems, most related to the fact that we would have to do a lot of cut-and-paste to get a script that included just primary and unique constraints, as well as indexes, and nothing else. What we really wanted was a script to create the tables with no indexes, then a script to add all the indexes, clustered key first, once the tables were loaded.

What I needed was a scripting tool that we could modify and ensure that the order of the indexes was what we wanted. I could have tried to navigate the SQL-DMO object model and call the scripting from there, just as Enterprise Manager does, but we risked running into the same inflexibility. Instead, I made a tool using Transact-SQL, which you'll see here, though in a much more refined and tested form.

Enterprise Manager Scripting

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 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.

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.

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.

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.

Scripting an Index

To generate the actual index script, usp_ScriptIndexes calls usp_ScriptOneIndex 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 shows usp_GenerateOneIndex.

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:

And IndexProperty(Object_Id('dbo.[authors]'), '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!

Using System Functions Instead of System Tables

It's a good Transact-SQL coding practice to make reference to properties of database objects through calls to system functions whenever possible, and avoiding direct references to system tables. Using Transact-SQL's own built-in functions like ObjectProperty() and IndexProperty() helps insulate your code from undocumented changes to the system tables that might appear in a service pack or new release. Unlike the ServerProperty() and DatabasePropertyEX() functions, you must execute ObjectProperty() and IndexProperty() in the database that contains the objects you want information about. Consequently, you need to use dynamic SQL if you must accept varying database contexts for your code.

Delimiting Strings

The SQL language is a little odd about delimiting strings. Under standard ANSI SQL, you cannot use double quotes to delimit strings, whereas you can use single quotes. Instead, you must use double quotes to delimit table or column names that include characters, like spaces, that would make them illegal names otherwise. Older versions of Transact-SQL, though, used the double quote as a delimiter, so the SET QUOTED_IDENTIFIER option was introduced in order to determine whether the ANSI SQL standard was being followed, or the older T-SQL. In SQL Server 7.0 and 2000, the default is for QUOTED_IDENTIFER to be ON, so double quotes cannot delimit strings. With SQL 7.0, Microsoft introduced the left and right square brackets, which you can use to delimit table and column names no matter what the setting of QUOTED_IDENTIFER happens to be. For that reason, the code in this article uses the brackets rather than double quotes to delimit strings. In fact, I don't use double quotes at all in the code so it doesn't matter how you set QUOTED_IDENFITER for these procedures and the generated scripts.

Listing 1. Getting index information

Use Utilities
Set Nocount On
Go

If Object_ID('dbo.usp_GetIndexInfo', 'P') Is Not Null
  Drop Procedure dbo.usp_GetIndexInfo
Go
Create Procedure dbo.usp_GetIndexInfo
    @DbName varchar(40)
  , @IncludeStatistics tinyint
  , @Debug tinyint = 0
As


Declare
  @SqlString varchar(4000)

-- Fill temp table with sysindexes information
Set @SqlString = '
Use ' + @DbName + '
Declare
    @TableID int
  , @TableOwner nvarchar(128)
  , @TableName nvarchar(128)
  , @KeyString nvarchar(2048)
  , @IndexName nvarchar(128)
  , @SqlString nvarchar(2000)
  , @cr char(1)


Insert Into dbo.#TempSysindexes
  Select
    DB_NAME() As DbName
  , si.id
  , Object_name(si.id)
  , User_Name(ObjectProperty(si.id, ''OwnerId''))
  , si.name As IndexName
  , si.indid As IndexID
  , '''' As IndexKeyString
  , Coalesce(ObjectProperty(Object_id(si.name), ''IsPrimaryKey''), 0)
  , Coalesce(ObjectProperty(Object_id(si.name), ''CnstIsClustKey''), 0)
  , Coalesce(ObjectProperty(Object_id(si.name), ''IsUniqueCnst''), 0)
  , Coalesce(IndexProperty(si.id, name, ''IndexFillFactor''), 0)
  , Coalesce(IndexProperty(si.id, name, ''IsClustered''), 0)
  , Coalesce(IndexProperty(si.id, name, ''IsStatistics''), 0)
  , Coalesce(IndexProperty(si.id, name, ''IsAutoStatistics''), 0)
  , Coalesce(IndexProperty(si.id, name, ''IsHypothetical''), 0)
  , Coalesce(IndexProperty(si.id, name, ''IsPadIndex''), 0)
  , Coalesce(IndexProperty(si.id, name, ''IsUnique''), 0)
  , Coalesce(IndexProperty(si.id, name, ''IsPageLockDisallowed''), 0)
  , Coalesce(IndexProperty(si.id, name, ''IsRowLockDisallowed''), 0)
  , Coalesce(ObjectProperty(si.id, ''CnstIsNonclustKey''), 0)
  , Coalesce(ObjectProperty(si.id, ''CnstIsNotTrusted''), 0)
  , groupid
  , '''' As FileGroupName
  From sysindexes si
  Where ObjectProperty(si.id, ''IsUserTable'') = 1
  And ObjectProperty(si.id, ''TableHasIndex'') = 1
  And Object_name(si.id) Not Like ''dtproperties''
  And si.indid > 0 and si.indid < 255'
  + Case When @IncludeStatistics = 0 Then '
  And IndexProperty(si.id, name, ''IsStatistics'') = 0' Else '' End +'
  Order By 1,2

Set @TableID = (Select Min(TableID)
  From dbo.#TempSysindexes
  Where IsHypothetical =0)
Select @TableOwner = TableOwner, @TableName = TableName
  From dbo.#TempSysindexes
  Where TableID = @TableID
While @TableID Is Not Null
Begin
  Set @IndexName = (Select Min(IndexName)
    From dbo.#TempSysindexes
    Where TableID = @TableID And IsHypothetical =0)
  While @IndexName Is Not Null
  Begin
    Exec Utilities.dbo.usp_GetIndexKeys ' + @DbName+ ', @TableOwner, @TableName, @IndexName, @KeyString OUTPUT, 0
    If @KeyString Is Not Null
      Update dbo.#TempSysindexes
      Set IndexKeyString = @KeyString
      Where TableID = @TableID And IndexName = @IndexName
    Update dbo.#TempSysindexes
      Set FileGroupName = (Select groupname from sysfilegroups where groupid = #TempSysindexes.FileGroupID)
      Where TableID = @TableID and IndexName = @IndexName

    Set @IndexName = (Select Min(IndexName)
      From dbo.#TempSysindexes
      Where TableID = @TableID And IsHypothetical =0
        And IndexName > @IndexName)
  End
  Set @TableID = (Select Min(TableID)
    From dbo.#TempSysindexes
    Where IsHypothetical =0
      And TableID > @TableID)
  Select @TableOwner = TableOwner, @TableName = TableName
    From dbo.#TempSysindexes
    Where TableID = @TableID

End'
If @Debug = 1 Print @SqlString
Exec(@SqlString)

If @Debug = 1 Select * From dbo.#TempSysindexes

Return

Listing 2. Getting index column information

Use Utilities
go

If Object_ID('dbo.usp_GetIndexColumns', 'P') Is Not Null
  Drop Procedure dbo.usp_GetIndexColumns
Go
Create Procedure dbo.usp_GetIndexColumns
    @DbName varchar(40)
  , @TableOwner nvarchar(128)
  , @TableName nvarchar(128)
  , @IndName varchar(100)
  , @KeyString varchar(1000) OUTPUT
  , @Debug tinyint = 0
As
-- Declare variables to use in this example.
DECLARE @KeyNo int
  , @cmd varchar(1000)

CREATE TABLE #keycolumns (
  KeyNo int
  , ColName sysname
  , ColOrderDesc tinyint)


Set @cmd = '
  Use ' + @DbName + '
  Declare @KeysTableID int
  Set @KeysTableID = Object_ID(''' + rtrim(@TableOwner) + '.' + rtrim(@TableName) + ''')
  Insert Into #keycolumns
  Select
      sk.keyno
    , ''['' + Rtrim(sc.name) + '']''
    , Case When Indexkey_Property(@KeysTableID, si.indid, sk.keyno, ''IsDescending'') = 1 Then 1 Else 0 End
    From ' + @DbName + '.dbo.Syscolumns sc
    Join ' + @DbName + '.dbo.Sysobjects so
      On so.id = sc.id And so.id = @KeysTableID
    Join ' + @DbName + '.dbo.Sysindexes si
      On si.id = so.id And si.name = ''' + @IndName + '''
    Join ' + @DbName + '.dbo.Sysindexkeys sk
      On sk.id = so.id
      And sk.indid = si.indid
      And sk.colid = sc.colid
  Order by sk.keyno'
If @Debug = 1 Print @cmd
Exec (@cmd)
If @Debug = 1 Select * From #keycolumns
-- Create the string
Set @KeyNo = Null
Set @KeyNo = (Select Min(KeyNo) From #Keycolumns)
If @KeyNo Is Not Null
  Begin
  Set @KeyString = (Select ColName + Case When ColOrderDesc = 1 Then ' DESC' Else '' End
    From #Keycolumns Where KeyNo = @KeyNo)
  End
Set @KeyNo = (Select Min(KeyNo) From #Keycolumns Where KeyNo > @KeyNo)
While @KeyNo Is Not Null
Begin
  Set @KeyString = @KeyString + ', ' + (Select ColName + Case When ColOrderDesc = 1 Then ' DESC' Else '' End
    From #Keycolumns Where KeyNo = @KeyNo)
  If @Debug = 1 Print @KeyString
  Set @KeyNo = (Select Min(KeyNo) From #Keycolumns Where KeyNo > @KeyNo)
End

Listing 3. Generating one index's script

Use Utilities
Set Nocount On
Go

If Object_ID('dbo.usp_ScriptOneIndex', 'P') Is Not Null
  Drop Procedure dbo.usp_ScriptOneIndex
Go
Create Procedure dbo.usp_ScriptOneIndex
    @DbName nvarchar(128)
  , @TableName nvarchar(128)
  , @TableOwner nvarchar(128)
  , @IndexName nvarchar(128)
  , @Create tinyint
  , @Drop tinyint
  , @Debug tinyint = 0
As

Declare
  @SqlString varchar(4000)
  , @Message varchar(100)
  , @cr char(1)
  , @IsPrimaryKey tinyint
  , @IsUniqueConstraint tinyint
  , @IsUniqueIndex tinyint
  , @IsClustered tinyint
  , @IsStatistics tinyint
  , @CnstIsNotTrusted tinyint
  , @IndexFillFactor tinyint
  , @IndexKeyString nvarchar(1000)
  , @FileGroupName nvarchar(128)
Select
    @IsPrimaryKey = IsPrimaryKey
  , @IsUniqueConstraint = IsUniqueConstraint
  , @IsUniqueIndex = IsUniqueIndex
  , @IsClustered = IsClustered
  , @IsStatistics = IsStatistics
  , @IndexKeyString = IndexKeyString
  , @CnstIsNotTrusted = CnstIsNotTrusted
  , @IndexFillFactor = IndexFillFactor
  , @FileGroupName = FileGroupName
From dbo.#TempSysindexes
Where TableName = @TableName And IndexName = @IndexName And TableOwner = @TableOwner

Set @cr = CHAR(13)

-- Test parameters:
If @IsStatistics = 1 And (@IsPrimaryKey = 1 Or @IsUniqueConstraint = 1 Or @IsUniqueIndex =1 Or @IsClustered = 1)
  Begin
    Set @Message = 'Statistics cannot have index properties'
  Select @IndexName
    Goto ErrorLabel
  End
If @IsPrimaryKey = 1 And @IsUniqueConstraint = 1
  Begin
    Set @Message = 'You cannot have both a primary key and a unique constraint'
    Goto ErrorLabel
  End

-- Test Drop first
If @Drop = 1
  If @IsStatistics = 0
    If @IsPrimaryKey = 1 Or @IsUniqueConstraint = 1
      Begin
        Set @SqlString = 'If Not Exists(Select * From INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS' + @cr
        Set @SqlString = @SqlString + ' Where Unique_Constraint_Name = ''' + @IndexName + ''') ' + @cr
        Set @SqlString = @SqlString + ' And IndexProperty(Object_Id(''' + @TableOwner+'.[' + @TableName + ']''), '''+ @IndexName + ''', ''IsClustered'') Is Not Null ' + @cr
        Set @SqlString = @SqlString + ' Alter Table ' + @DbName + '.' + @TableOwner + '.[' + @TableName + '] Drop Constraint ' + @IndexName + @cr
        Set @SqlString = @SqlString + 'Else' + @cr
        Set @SqlString = @SqlString + ' Begin' + @cr
        Set @SqlString = @SqlString + ' Print ''Could not drop index ' + @IndexName + ' for table ' + Rtrim(@TableOwner) + '.[' + Rtrim(@TableName) + ']''' + @cr
        Set @SqlString = @SqlString + ' Print '' because it is referenced by at least one foreign key constraint.'' ' + @cr
        Set @SqlString = @SqlString + ' Print ''Drop the foreign key constraint(s) first.''' + @cr
        Set @SqlString = @SqlString + ' Print ''''' + @cr
        Set @SqlString = @SqlString + ' End' + @cr
        Set @SqlString = @SqlString + 'Go ' + @cr
        Print @SqlString
      End
    Else
      Begin
        Set @SqlString = 'If IndexProperty(Object_Id(''' + @TableOwner+'.[' + @TableName + ']''), '''+ @IndexName + ''', ''IsClustered'') Is Not Null ' + @cr
        Set @SqlString = @SqlString + ' Drop Index [' + @TableName + '].' + @IndexName + @cr
        Set @SqlString = @SqlString + 'Go ' + @cr
        Print @SqlString
      End
  Else
    Begin
      Set @SqlString = 'If IndexProperty(Object_Id(''' + @TableOwner+'.[' + @TableName + ']''), '''+ @IndexName + ''', ''IsStatistics'') Is Not Null ' + @cr
      Set @SqlString = @SqlString + ' Drop Statistics [' + @TableName + '].' + @IndexName + @cr
      Set @SqlString = @SqlString + 'Go ' + @cr
      Print @SqlString
    End


-- Now test for Create
If @Create = 1
  If @IsStatistics = 0
    If @IsPrimaryKey = 1 Or @IsUniqueConstraint = 1
      Begin
        Set @SqlString = 'If IndexProperty(Object_Id(''' + @TableOwner+'.['+ @TableName + ']''), '''+ @IndexName + ''', ''IsClustered'') Is Null ' + @cr
        Set @SqlString = @SqlString + ' Alter Table ' + @DbName + '.[' + @TableOwner + '].' + @TableName + @cr
        Set @SqlString = @SqlString + Case When @CnstIsNotTrusted = 1 Then ' With Nocheck ' Else '' End
        Set @SqlString = @SqlString + ' Add Constraint ' + @IndexName
        Set @SqlString = @SqlString + Case When @IsPrimaryKey = 1 Then ' Primary Key ' Else ' Unique ' End
        Set @SqlString = @SqlString + Case When @IsClustered = 1 Then ' Clustered ' Else ' NonClustered ' End
        Set @SqlString = @SqlString + '(' + @IndexKeyString + ')' + @cr
        Set @SqlString = @SqlString + Case When @IndexFillFactor > 0 then ' With Fillfactor = ' + Cast(@IndexFillFactor As varchar(5)) + @cr Else '' End
        Set @SqlString = @SqlString + ' On [' + @FilegroupName + ']' + @cr
        Set @SqlString = @SqlString + @cr + 'Go ' + @cr + @cr
        Print @SqlString
      End
    Else
      Begin
        Set @SqlString = 'If IndexProperty(Object_Id(''' + @TableOwner+'.['+ @TableName + ']''), '''+ @IndexName + ''', ''IsClustered'') Is Null ' + @cr
        Set @SqlString = @SqlString + ' Create'
        Set @SqlString = @SqlString + Case When @IsUniqueIndex = 1 Then ' Unique ' Else '' End
        Set @SqlString = @SqlString + Case When @IsClustered = 1 Then ' Clustered ' Else ' NonClustered ' End
        Set @SqlString = @SqlString + 'Index ' + @IndexName + ' On ' + @TableOwner+'.['+@TableName +']'
        Set @SqlString = @SqlString + ' (' + @IndexKeyString + ')' + @cr
        Set @SqlString = @SqlString + Case When @IndexFillFactor > 0 then ' With Fillfactor = ' + Cast(@IndexFillFactor As varchar(5)) + @cr Else '' End
        Set @SqlString = @SqlString + ' On [' + @FilegroupName + ']' + @cr
        Set @SqlString = @SqlString + 'Go ' + @cr
        Print @SqlString
      End
  Else
    Begin
      Set @SqlString = 'If IndexProperty(Object_Id(''' + @TableOwner+'.['+ @TableName + ']''), '''+ @IndexName + ''', ''IsStatistics'') Is Null ' + @cr
      Set @SqlString = @SqlString + ' Create Statistics ' + @IndexName + ' On ' + @TableOwner+'.['+ @TableName +']'
      Set @SqlString = @SqlString + ' (' + @IndexKeyString + ')' + @cr
      Set @SqlString = @SqlString + 'Go ' + @cr
      Print @SqlString
    End
Print @cr
Return

ErrorLabel:
  RaisError(@Message, 1,1)
  Return

Listing 4. Sample output for the pubs..authors table

-- dbo.authors
----------------------------------------
If Not Exists(Select * From
  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
    Where Unique_Constraint_Name = 'UPKCL_auidind')
    And IndexProperty(Object_Id('dbo.[authors]'), 'UPKCL_auidind',
       'IsClustered') Is Not Null
  Alter Table Pubscopy2.dbo.[authors] Drop Constraint UPKCL_auidind

Else
  Begin
    Print 'Could not drop index UPKCL_auidind for table dbo.[authors]'
    Print ' because it is referenced by at least one foreign key constraint.'
    Print 'Drop the foreign key constraint(s) first.'
    Print ''
  End
Go

If IndexProperty(Object_Id('dbo.[authors]'), 'UPKCL_auidind',
  'IsClustered') Is Null
  Alter Table Pubscopy2.[dbo].authors
    Add Constraint UPKCL_auidind Primary Key
    Clustered ([au_id]) On [PRIMARY]
Go

If IndexProperty(Object_Id('dbo.[authors]'),
  '_WA_Sys_address_75D7831F', 'IsStatistics')
  Is Not Null
    Drop Statistics [authors]._WA_Sys_address_75D7831F
Go

If IndexProperty(Object_Id('dbo.[authors]'),
  '_WA_Sys_address_75D7831F', 'IsStatistics') Is Null
    Create Statistics _WA_Sys_address_75D7831F
    On dbo.[authors] ([address])
Go