February 24, 2011 at 3:53 am
Hi,
Assuming i have a user table with userid, loginname, firstname, lastname etc..,
now i will have to add index for the column lognname, i need to write a script so that index should be created if loginname in the user table doesnot have any index.
February 24, 2011 at 5:39 am
Try this script that list the columns of a table in any index:
select column_name = c.name
from sys.index_columns ic
inner join
sys.columns c
on ic.object_id = c.object_id and ic.column_id = c.column_id
where object_name(ic.object_id) = '<your table name>'
February 24, 2011 at 6:13 am
Hi,
thanks a lot, i just modified after i got your query. it was really usefull thank you.
select column_name = c.name, object_name(ic.object_id), Si.Name
from sys.index_columns ic
inner join
sys.columns c
on ic.object_id = c.object_id and ic.column_id = c.column_id
inner join sys.indexes si on object_name(si.object_id)=object_name(ic.object_id)
AND object_name(si.object_id)=object_name(c.object_id) --and si.type=2
where object_name(ic.object_id) = 'tablename' AND c.name='columnname'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply