SQL SERVER – Find Rows and Index Count – SQL in Sixty Seconds #029 – Video

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:

SQL SERVER - Find Rows and Index Count - SQL in Sixty Seconds #029 - Video 29-800x450

  • 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:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , , ,
Previous Post
SQL SERVER – 3 Online SQL Courses at Pluralsight and Free Learning Resources
Next Post
SQL SERVER – Concurrency Basics – Guest Post by Vinod Kumar

Related Posts

11 Comments. Leave new

  • Satya Chaitanya
    October 10, 2012 9:45 am

    Very informative….

    what is meant by logical sorting and physical sorting of data in terms of index..

    Reply
  • This script returns only details of tables which do not have any non-clustered indexes.

    Script should be:

    SELECT [schema_name] = s.name, table_name = o.name,
    MAX(i1.type_desc) ClusteredIndexorHeap,
    COUNT(i.TYPE) NoOfNonClusteredIndex, p.rows
    FROM sys.indexes i
    RIGHT JOIN sys.objects o ON i.[object_id] = o.[object_id] AND i.TYPE=2
    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 o.OBJECT_ID = i1.OBJECT_ID AND i1.TYPE IN (0,1)
    WHERE o.TYPE IN (‘U’)
    GROUP BY s.name, o.name, p.rows
    ORDER BY schema_name, table_name

    Reply
  • Rémi BOURGAREL
    October 10, 2012 1:32 pm

    The video is available anymore, can you send it again ?

    Reply
  • I think rather than the toal number of non clustered indexes, it returns the total number of statistics on that table !

    Reply
  • Hi sir , can you please help me on below requirement

    I had two servers

    server 1 ——-SSIS Package is present
    server 2—– EXe location

    I had SSIS Package in server 1 , at one step am calling the exe present in server 2 and passing arguments and working directory to server 2

    when i execute the package the package should execute in Server 1, but when it trigger to exe the exe should execute in server 2 not in server 1

    can any one help me on these i can use dotnet also

    thanks in advance

    Reply
  • The count of nonclustered index is actually returning total count of index type which includes Heap, clustered, non clustered.

    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

    Now the column of NoOfNonClusteredIndexes will return count of non clustered indexes only.

    The query may be further optimized.

    Reply
    • Yes, In modified script, type=2 portion is missing from right join , this change will be enough to get count of non-clustered indexes

      Reply
  • Is there any possibility to get the row count based on table column values as parameter.
    example

    SELECT *
    FROM bigTransactionHistory
    where column1 = ‘ ‘

    Joing with your query??

    Reply

Leave a Reply

Menu