query to find tables with indexes

Question

query to find tables with indexes

shahgols

SSCrazy Eights

Points: 9874
More actions
July 27, 2006 at 3:47 pm

#61433

Hi everyone,
I am having the hardest time putting together a query that will return the names of all tables that have indexes. Here's the query, what am I doing wrong?
select distinct a.table_name from
information_schema.tables a,sysindexes b
where a.table_type = 'BASE TABLE' and
a.table_name = object_name (b.id) and
(b.indid > 0 and indid < 255) Thank you!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lance Sanders SSC Eights! Points: 903 More actions · Answer 1

Your query is including table statistics. You need to add:

  and (status & 64)=0

or, eliminating the join:

  select distinct object_name(id) 
  from sysindexes 
  where indid > 0 and indid < 255 
  and (status & 64)=0 
  and objectproperty(id,'IsUserTable')=1

Purushotham Kumar Katakam SSC Enthusiast Points: 172 More actions · Answer 2

Pls. check whether this query may help u or not.

select so.name,

si.name from sysobjects so

join sysindexes si on so.id = si.id where so.xtype !='S'

Thanks & Regards,

Kumar KP

Thanks & Regards,
9989069383
Katakam.

shahgols SSCrazy Eights Points: 9874 More actions · Answer 3

Hi there,

Thank you to both of you for your help!

Lance, your query worked great! Except it also returned the table dtproperties, which doesn't have an index on it. I wonder why.

Kumar, your query was not what I was looking for. I was looking for a query that will return the tables that have an index on them. Thanks for your attempt though!

WesleyB Ten Centuries Points: 1307 More actions · Answer 4

Hi,

SELECT

TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIndex') = 1

But dtproperties does have an index.

pk_dtproperties clustered, unique, primary key located on PRIMARY id, property

shahgols SSCrazy Eights Points: 9874 More actions · Answer 5

Thank you Wesley, that query works for me too.

On my servers dtproperties doesn't have any indexes. Could it be that in your environment this table was modified?

WesleyB Ten Centuries Points: 1307 More actions · Answer 6

Weird indeed. It has a PK in both SQL Server 2000 and SQL Server 2005 here.