Hi Guys,
I need help to get the actual script of the index using some sql command or sql query. As we know sp_helptext 'Proc_Name' .
will give us procedure acutal code. i need to do with index. but not sure how to do that. Where actually indexes are stored so i can do query on the table and get its script by sql query. Or if any one know the way how to retrive it.
Here is query which gives me the all Table Name, Index Name, Index Id Column Name, Column Id
SELECT
t.name as TableName,
ind.name as IndexName,
ind.index_id as IndexId,
ic.index_column_id as ColumnsID,
col.name as ColumnName
From
sys.indexes ind
INNER JOIN
sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id= ic.index_id
INNER JOIN
sys.columns col on ic.object_id = col.object_id and ic.column_id= col.column_id
inner join sys.tables t on ind.object_id = t.object_id
WHERE
ind.is_primary_key=0
and ind.is_unique=0
and ind.is_unique_constraint=0
and t.is_ms_shipped=0
ORDER BY
t.name, ind.name, ind.index_id, ic.index_column_id.
I know the following way were we get index can be scripted
Under Object Explorer -> Databases -> Database -> Tables - > Indexes -> right-click on index - > Create Script as ..
but i want to do it with query which is fired by my console application
Thanks
3 Replies
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Raja TPosted Aug 5, 2015, 1:01 AM
rahul ahujaPosted Aug 4, 2015, 9:54 AM
Raja TPosted Jul 31, 2015, 10:36 PM
select * from sys.indexes
select * from sys.index_columns
Check out bellow URLs
http://stackoverflow.com/questions/765867/list-of-all-index-index-columns-in-sql-server-db
https://gallery.technet.microsoft.com/scriptcenter/SQL-Server-Generate-Index-fa790441