Getting Object Counts

Recently I was reporting a bug I noticed with a 3rd party tool, the support group asked me to send them "the number of instances, number of databases per instance, total number of tables and total number of indexes." Now I knew the number of instances I was monitoring, and I knew the approximate number of tables (perhaps a good DBA should know this number, not sure...do other DBA's out there with larger servers know the exact number? comment below, I'm interested), but I had no idea how many indexes. So I wrote a little script to give me this number and as I like to do, I'm making it available to the community, hope this helps you...if you find use of it (even years in the future) post below.

/***********************************
 ** Author: Eric Zierdt           **
 ** Date:   4/21/2011             **
 ** Email:  eric.zierdt@gmail.com **
 ***********************************/

DECLARE @CountAll AS TABLE (CntName VARCHAR(100), Cnt INT)

/*******************************************
 ** GET COUNT OF ALL NON-SYSTEM DATABASES **
 *******************************************/
INSERT INTO @CountAll
SELECT 'TotalDBs',COUNT(1) AS TotalDBs
FROM sys.databases D
WHERE owner_sid <> '0x01'


/******************************
 ** GET COUNT OF ALL TABLES  **
 ** FOR NON-SYSTEM DATABASES **
 ******************************/
DECLARE @CountTable AS TABLE (DB VARCHAR(100),CNT INT)
DECLARE @SQL VARCHAR(MAX)
DECLARE @DBName VARCHAR(128)
SET @DBName = ''
WHILE @DBName < (
 SELECT MIN(name) 
 FROM sys.databases
 WHERE name > @DBName 
   AND name NOT IN ('master','model','msdb','tempdb')
)
BEGIN
 SELECT @DBName = MIN(NAME) FROM sys.databases WHERE name > @DBName AND NAME NOT IN ('master','model','msdb','tempdb')
 SET @SQL = '
  USE [' + @DBName + ']
  SELECT ''' + @DBName + ''' AS DBName
    ,COUNT(1) AS Cnt
  FROM   sys.tables
 '
 INSERT INTO @CountTable
 EXEC (@SQL)
END

INSERT INTO @CountAll
SELECT 'TotalTables',SUM(CNT) AS TotalTables 
FROM @CountTable

/*******************************
 ** GET COUNT OF ALL INDEXES  **
 ** FOR NON-SYSTEM DATABASES  **
 *******************************/
DECLARE @IndexTable AS TABLE (DB VARCHAR(100),TableName VARCHAR(100), CNT INT)
SET @DBName = ''
WHILE @DBName < (
 SELECT MIN(name) 
 FROM sys.databases
 WHERE name > @DBName 
   AND name NOT IN ('master','model','msdb','tempdb')
)
BEGIN
 SELECT @DBName = MIN(NAME) FROM sys.databases WHERE name > @DBName AND NAME NOT IN ('master','model','msdb','tempdb')
 SET @SQL = '
  USE [' + @DBName + ']
  SELECT ''' + @DBName + ''' AS DBName
    ,OBJECT_NAME(I.object_id) AS TableName
    , COUNT(1) AS Cnt
  FROM sys.indexes I
  JOIN sys.tables T ON I.object_id = T.object_id
  WHERE I.name IS NOT NULL
  GROUP BY OBJECT_NAME(I.object_id)
 '
 --PRINT @SQL
 INSERT INTO @IndexTable
 EXEC (@SQL)
END

INSERT INTO @CountAll
SELECT 'TotalIndexes',SUM(CNT) AS TotalIndexes 
FROM @IndexTable

SELECT * 
FROM @CountAll