How to see index coding

Question

Post reply

How to see index coding

Anand BI Developer

SSCommitted

Points: 1772
More actions
December 15, 2016 at 4:14 am

#324052

Hi Experts ,
sp_helptext view_name
sp_helptext Procedure_name
but how can i see index code :
sp_helptext index_name --> i got error from this code

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 1

John Mitchell-245523

SSC Guru

Points: 148786

December 15, 2016 at 4:34 am

#1918126

Have you tried sp_helpindex?

John

Anand BI Developer SSCommitted Points: 1772 More actions · Answer 2

No John ,

i dont try before , i just wanna indexcoding for given index_name which one i have created

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 3

So you want the CREATE INDEX statement for the given index? You could right-click on the index in SSMS and choose Script Index As. If you want it to be repeatable, run a trace or Extended Events session to see what query is executed behind the scenes to get the definition. Or you could type something like "generate create index script" into your favourite search engine to find something that someone else has written. I have such a query myself that I got from the internet, but I can't remember exactly where and I don't want to post it without crediting the author.

John

joeroshan SSChampion Points: 10377 More actions · Answer 4

You do not definition of the index as a script like that if that is what you are looking for.

You can right click the index in SSMS and script index as create to a query window.

Edit: just saw John already mentioned the same

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Shekhu Right there with Babe Points: 780 More actions · Answer 5

There are scripts to fetch the scripts for an index but its a big code , not a MS provided procedure/function like sp_helptext... you can refer one in below link... just limit it to your table

However, if you requirement is just limited to getting the code for the Index you created then follow the steps in this attachment

Anand BI Developer SSCommitted Points: 1772 More actions · Answer 6

Dear Shekhu ,

Thank you very much for your valuable time . your code is working well. it showing clustered & nonclustered index only , i want to see (unique,nonclustered & nonunique nonclustered index) also how can i change.

Shekhu Right there with Babe Points: 780 More actions · Answer 7

Hi Anand, Good to know that the query helped you. Indexes are categorized only in 2 categories Clustered and non-clustered Index. However, when you create an INDEX and you mention the keyword UNIQUE it will be created as UNIQUE clustered or non-clustered index. This script will give you the UNIQUE keyword before the CLUSTERED/NONCLUSTERD Keyword .

Something like below

CREATE UNIQUE NONCLUSTERED INDEX IX_IndexName ON dbo.TableName ( COl1 ASC , Col2 ASC ) INCLUDE ( Col3 ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 80 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , DROP_EXISTING = ON , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ]

Ensure that your database has unique indexes. This script will surely list that

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 8

Anandkumar-SQL_Developer (12/15/2016)
Dear Shekhu ,
Thank you very much for your valuable time . your code is working well. it showing clustered & nonclustered index only , i want to see (unique,nonclustered & nonunique nonclustered index) also how can i change.

So you only want to see non-clustered indexes? Heaps are shown with index_id 0, clustered indexes with index_id 1 and non-clustered indexes with index_id 2 and greater. Therefore all you need to do is filter on index_id.

John