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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy