October 24, 2006 at 1:35 pm
I have like more than 500 tables & want to know INDEXES for those individual Tables if any INDEXES are created or not or do the exists. any help about System Store Procedure or SQL Script will be appreciated. Thanking in advance.
October 24, 2006 at 2:05 pm
Query SysIndexes (look in BOL for the columns that you want to display). Join SysIndexes to SysObjects for Table names. I am not in a place where I have access to a SQL Server so I can't post the join for you, but it's a very straight forward query.
October 24, 2006 at 2:34 pm
Something like this:
select
HasIndex=case when idx.id is null then 0 else 1 end,
TableName=tbl.name,
IndexName=isnull(idx.name, '*** No Indexes ***')
from sysobjects tbl
left join (select * from sysindexes where not [name] like '[_]WA%' and not indid in (0,255)) idx on idx.id = tbl.id
where tbl.type='U'
order by 1, 2, idx.indid
You can also add in a join to sysindexkeys and syscolumns if you want
October 24, 2006 at 4:22 pm
Thanks for your all help & reply & especially for the script that you gave was helpfull & thanks to mention System tables name.
October 24, 2006 at 5:54 pm
there is also a query wizard in the query analyzer...
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply