August 1, 2005 at 4:44 pm
I need to select index form sysindexes by the table_name and column_name.
What system view I could use to join to sysindexes to give me only the index I need?
This is the beginning of my statement:
declare @tab_name varchar (100)
declare @coulmn_name varchar (100)
select @tab_name= 'ADDRESS'
select @coulmn_name = 'ADDR_id'
select *--name
from sysindexes
where sysindexes.id = object_id(@tab_name) and indid > 0 and indid < 255 and (sysindexes.status & 64)=0
August 2, 2005 at 2:53 am
SELECT i.*
FROM sysindexkeys ik
INNER JOIN sysindexes i ON ik.indid = i.indid AND ik.[id] = i.[id]
INNER JOIN syscolumns c ON ik.colid = c.colid AND ik.[id] = c.[id]
WHERE ik.[id] = OBJECT_ID(@table_name)
AND c.[name] = @column_name
August 2, 2005 at 9:58 am
Thanks a lot, but is there a way to select the index code/ all columns which the index is build upon. These way before to drop it, I could have already all details and I can rebuild it easily in the end...
Thanks again.
Mj
August 2, 2005 at 11:25 am
I am not quite sure what you mean, but I think you are looking to script an index. Right? I think you could use sysindexkeys for that information.
August 2, 2005 at 11:40 am
Does this help?
select tbl.[name] as TableName,
idx.[name] as IndexName,
indexproperty( tbl.[id], idx.[name], 'IsStatistics') as IsStats,
indexproperty( tbl.[id], idx.[name], 'IsAutoStatistics') as IsAutoStats,
indexproperty( tbl.[id], idx.[name], 'IsHypothetical') as IsHypothetical,
indexproperty( tbl.[id], idx.[name], 'IsClustered') as IsClustered,
index_col( tbl.[name], idx.indid, 1 ) as col1,
index_col( tbl.[name], idx.indid, 2 ) as col2,
index_col( tbl.[name], idx.indid, 3 ) as col3,
index_col( tbl.[name], idx.indid, 4 ) as col4,
index_col( tbl.[name], idx.indid, 5 ) as col5,
index_col( tbl.[name], idx.indid, 6 ) as col6,
index_col( tbl.[name], idx.indid, 7 ) as col7,
index_col( tbl.[name], idx.indid, 8 ) as col8,
index_col( tbl.[name], idx.indid, 9 ) as col9,
index_col( tbl.[name], idx.indid, 10 ) as col10,
index_col( tbl.[name], idx.indid, 11 ) as col11,
index_col( tbl.[name], idx.indid, 12 ) as col12,
index_col( tbl.[name], idx.indid, 13 ) as col13,
index_col( tbl.[name], idx.indid, 14 ) as col14,
index_col( tbl.[name], idx.indid, 15 ) as col15,
index_col( tbl.[name], idx.indid, 16 ) as col16,
dpages,
used,
rowcnt
from sysindexes idx
inner join sysobjects tbl on idx.[id] = tbl.[id]
where indid > 0 --Tables are 0, indexes are greater
order by tableName, indexname
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply