September 10, 2009 at 12:42 pm
Hello Guys,
I am using SQL SERver 2005.
I need following information across the database.
Table_name, Column_Name,Index_name,Index_type, Index_path
I request you to provide me a script which should give above columns in the result for all the tables in the database.
TIA
Jus
September 10, 2009 at 12:52 pm
You should take as look at sysobjects on your DB. You can use the parent_obj to get the name of table to which the index is connected
-Roy
September 10, 2009 at 1:05 pm
Thx Roy....I will look into it...anyway..if you have proven script with you..can you post it here....
September 10, 2009 at 1:34 pm
try these 2 system tables also
sys.indexes ---- sys.index_columns
:crazy: :alien:
Umar Iqbal
September 10, 2009 at 1:45 pm
Jus, Umar and Me both gave you the right direction, please try to figure out from these two tables. Thats the only way to learn. If you find any problems while trying to do this, come back and ask a question, we will help you with more details.
-Roy
September 10, 2009 at 2:10 pm
Roy / Umar:
Does the following script good enough to serve the purpose or does it require any changes?
select distinct a.id,a.name,b.name,d.name,b.type_desc from sys.sysobjects a
inner join sys.syscolumns d on a.id = d.id
inner join sys.indexes b on a.id = b.object_id
inner join sys.index_columns c on d.colid = c.column_id
where a.xtype='u'
TIA
Jus
September 10, 2009 at 2:19 pm
looks fine but look at execution plan it will take long time and put load on disk . too many joins. reduce if you can or add more conditional clause. if you looking for particular table then put it in conditional clause so it can use seek operation its currently using scan
:crazy: :alien:
Umar Iqbal
September 10, 2009 at 2:27 pm
Thx..I will take care of that...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply