Having discuss indexes in some what details, this post will give some queries to get the index information of you databases.

Ok, if you are a DBA, most of the time you need to manage some others databases. In my carrier, it is only few occasions where I was given task to design fresh database. All the other occasions, it is managing heritage databases, which you can’t avoid.

So in this case, you don’t know, what are the indexes, which table has which index so on.

You need some queries to get that information.

We discussed that it is much much better to have at least a primary key for a table. If you want to list out table which does not have primary key, this is the query to get the list of tables with no primary keys.

SELECT  Object_Schema_name(object_ID) + '.' + name AS [tables with no primary keys]
FROM    sys.tables
WHERE   OBJECTPROPERTY(object_id, 'TableHasPrimaryKey') = 0
ORDER BY 1

Next thing you look for is indexes and following query will list you the tables which you don’t have at least one index.

SELECT  Object_Schema_name(object_ID) + '.' + name AS [Tables with no indexes]
FROM    sys.tables
WHERE   OBJECTPROPERTY(object_id, 'TableHasIndex') = 0
ORDER BY 1

In the previous post we discussed effect on many indexes. So if you want to find out which tables have most indexes, execute the following query.

SELECT TOP 5
        Object_Schema_name(t.object_ID) + '.' + t.name AS [Table],
        COUNT(1) AS [Indexes]
FROM    sys.indexes i
        INNER JOIN sys.objects t ON i.object_ID = t.object_ID
GROUP BY Object_Schema_name(t.object_ID) + '.' + t.name
ORDER BY COUNT(1) DESC

If you closely analyze these queries, you can see you are using two system tables sys.tables and sys.indexes. then we are using two system functions, OBJECT_SCHEMA_NAME and OBJECTPROPERTY.