Add Index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Add Index

Author

Topic

gregoryagu
Yak Posting Veteran

80 Posts

Posted - 2008-08-01 : 11:14:40

-A Utility to Add an Index, dropping it if it already exists.

Note that this SP calls Utils_DropIndex which is the subject of another post.

ALTER Procedure Utils_AddIndex
(
@IndexType varchar(128),
@TableName varchar(128),
@IndexName varchar(128),
@Column1 varchar(128),
@Column2 varchar(128) = '',
@Column3 varchar(128) = ''
)
AS
BEGIN

--First Drop It if it exists
Exec Utils_DropIndex @TableName, @IndexName
Declare @SQL varchar(4000)
Set @SQL = 'CREATE ' + @IndexType + ' INDEX '
+ '[' + @IndexName + '] ON [' + @TableName + ']'
+ '([' + @Column1 + '] ASC '

IF NOT @Column2 = ''
BEGIN
Set @SQL = @SQL + ', [' + @Column2 + '] ASC '
END

IF NOT @Column3 = ''
BEGIN
Set @SQL = @SQL + ', [' + @Column3 + '] ASC '
END

Set @SQL = @SQL + ')'

print @SQL
Exec( @SQL )
Declare @strText varchar(100)
Set @strText = 'The Index ' + @IndexName + ' has been created.'
PRINT @strText
END

--Example Use
Exec Utils_AddIndex
'UNIQUE CLUSTERED',
'Activities',
'MyNewIndexName',
'objectid', 'subtype', 'startdate'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources