get the DB index SQL

[dBlue]

Hi all

is there any efficient way to get the T-SQL statement for an existing
index? In the EM, we can
1) right-click on a tablename -- All Tasks -- Manage Indexes;
2) select an index, then click on "Edit";
3) Click on "Edit SQL"

to get the SQL for the indexes as below

CREATE UNIQUE
INDEX [idxName] ON [dbo].[tblName] ([colName])
WITH
FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]

But, is there any other way; such as the sp_helptext we used for store
procedure, that can automatically generate the SQL (above) for us?

Thanks

Albion(052X)

[Erland Sommarskog]

dBlue (zk*****@hotmail.com) writes:

is there any efficient way to get the T-SQL statement for an existing
index? In the EM, we can
1) right-click on a tablename -- All Tasks -- Manage Indexes;
2) select an index, then click on "Edit";
3) Click on "Edit SQL"

to get the SQL for the indexes as below

CREATE UNIQUE
INDEX [idxName] ON [dbo].[tblName] ([colName])
WITH
FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]

But, is there any other way; such as the sp_helptext we used for store
procedure, that can automatically generate the SQL (above) for us?

The easiest is probably to spy on EM using Profiler. The SQL it
runs may not return the CREATE INDEX statements, but it may give you
the parts to work with.

The CREATE INDEX statement itself is not stored in SQL Server.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx