Thursday, April 21, 2011

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

No comments:

Post a Comment