There are a few questions I often get asked. I wonder how interesting is that in our daily life all of us have to often need the same kind of information at the same time. Here is the example of the similar questions about index count:
- How many users created tables are there in the database?
- How many non clustered indexes each of the tables in the database have?
- Is table Heap or has clustered index on it?
- How many rows each of the tables is contained in the database?
I finally wrote down a very quick script (in less than sixty seconds when I originally wrote it) which can answer above questions. I also created a very quick video to explain the results and how to execute the script.
Here is the complete complete script which I have used in the SQL in Sixty Seconds Video. Thanks Harsh for important Tip in the comment.
SELECT [schema_name] = s.name, table_name = o.name, MAX(i1.type_desc) ClusteredIndexorHeap, MAX(COALESCE(i2.NCIC,0)) NoOfNonClusteredIndex, p.rows FROM sys.indexes i RIGHT JOIN sys.objects o ON i.[object_id] = o.[object_id] INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id] LEFT JOIN sys.partitions p ON p.OBJECT_ID = o.OBJECT_ID AND p.index_id IN (0,1) LEFT JOIN sys.indexes i1 ON i.OBJECT_ID = i1.OBJECT_ID AND i1.TYPE IN (0,1) LEFT JOIN (SELECT object_id,COUNT(Index_id) NCIC FROM sys.indexes WHERE type = 2 GROUP BY object_id) I2 ON i.OBJECT_ID = i2.OBJECT_ID WHERE o.TYPE IN ('U') GROUP BY s.name, o.name, p.rows ORDER BY schema_name, table_name
Above script has small modification as suggested by Harsh, Vijay in the comment.
Related Tips in SQL in Sixty Seconds:
- Find Row Count in Table – Find Largest Table in Database
- Find Row Count in Table – Find Largest Table in Database – T-SQL
- Identify Numbers of Non Clustered Index on Tables for Entire Database
- Index Levels, Page Count, Record Count and DMV – sys.dm_db_index_physical_stats
- Index Levels and Delete Operations – Page Level Observation
What would you like to see in the next SQL in Sixty Seconds video?
Reference: Pinal Dave (https://blog.sqlauthority.com)