SQL Server Performance Forum – Threads Archive

Create Index using Scripts

Hello
I need to create Index for 60 tables of Same Structure.
Is there a way to create them using SQL Scripts instead of using Enterprise Manager Thanks and regards

I found out
CREATE UNIQUE CLUSTERED INDEX [INdexName] ON dbo.TableName ([RecID]) I tried Like this earlier
CREATE UNIQUE CLUSTERED INDEX [INdexName] ON dbo.TableName ([RecID])
WITH DROP_EXISTING ON [PRIMARY] But got an error and i realized that before creating one how could i use Drop Existing to ON.
I removed that first and created the index and then ran the script once again it worked.

Try this: USE DatabaseName
GO
IF EXISTS (SELECT name FROM sysindexes
WHERE name = ‘Index_Name’)
DROP INDEX TableName.Index_Name
GO Also look BOL (Books Online) for DROP/CREATE index.
Name
———
Dilli Grg (1 row(s) affected)

— replace the DatabaseName with actual database name and Index_Name with index name… USE DatabaseName
GO declare @tablename sysname, @sql Varchar(1000), @Index_Name sysname
select @Index_Name = ‘Index_Name’
DECLARE tables CURSOR FOR
SELECT o.name
FROM sysobjects o
join sysindexes i on o.id = i.id
WHERE i.name = @Index_Name OPEN tables
FETCH NEXT FROM tables INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
select @tablename = ‘sysobjects’
SELECT @sql = ‘IF EXISTS (SELECT name FROM sysindexes WHERE name = ‘[email protected]_Name+’) DROP INDEX ‘[email protected]+’.’[email protected]_Name
SELECT @sql
exec (@sql)
FETCH NEXT FROM tables INTO @tablename
END
CLOSE tables
DEALLOCATE tables MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Oh, I didn’t see the 60 tables or something from the first post. I just looked at the error message part.[<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />] <pre id="code"> Name ——— Dilli Grg (1 row(s) affected) </pre id="code">

Thanks Dilli and Mohammed Regards

]]>