Query to retrieve tables indexed in a specific database

Question

Can someone provide me a query in sql server to retrieve tables that were made for indexing for a specific database ....

Answer 1

Your question is somewhat unclear. This will return all tables with at least one index.

select DISTINCT OBJECT_NAME(object_id)  
from sys.indexes 
where type<>0

Or for SQL Server 2000

select DISTINCT OBJECT_NAME(id)   
from sysindexes 
where indid<>0

Answer 2

select object_name(object_id),* from sys.indexes where type <> 0

This will return you all the indexes available in your database. But be careful, it also lists system tables.

Answer 3

sys.indexes DMV , :

SELECT TableName = object_name(Object_Id)
     , IndexName = Name
     , IndexType = Type_Desc

 FROM sys.indexes

Type_Desc , , .

sys.tables :

SELECT TableName = st.Name
     , IndexName = si.name
     , IndexType = si.type_desc
  FROM SYS.indexes si
  JOIN SYS.tables st
    ON si.object_id  = st.object_id