I got a request from my manager to update a document we have here called our Service Catalog. The document is published by the IT department and lists all the major software programs we use, the business units that use them, whether or not they are 24×7 systems, and some other stats about our department. My job was to update the database section.

One of the items was the number of SQL Servers we have and their versions. This was relatively easy. The next piece of information I needed to update was the total number of databases we have. A bit trickier, but since I have my Drive Space Monitoring procedure in place, and that breaks out sizes by database, it was a simple matter to write a query to count the total number of databases we have.

The last piece of information I needed to provide was a count of “database objects.” Huh? I have no idea what this meant, and neither did my manager. Apparently it was put in the document by a DBA who has long since left. The size of the number didn’t really give me any clues either. We had about 600 database and the database object number was just over 7,000.

To me, a database object refers to something very specific – something that is stored in a database (excluding user data). So that would include all tables, views, stored procedures, triggers, foreign keys, constraints, logins, etc. If that was truly what this number represented, a value of 7,000 for 600 databases seems very low to me. Furthermore, I’m not even sure why this number would be in the document, unless it was to make it sound like the DBA had a huge domain to watch over.

Well, whatever the case, I decided to go ahead and update the number, although I will be adding a note to the document indicating the number represents the total number of user tables, views, and stored procedures in our environment.

The next step was determining the easiest way to get that data. I do not have a Central Configuration Server set up, which probably would have made this job a bit easier. So instead, I decided to use the multi-server query capabilities of SSMS. A slight problem was that my environment has a mix of SQL 2000, 2005, and 2008 R2 servers, so the basic system tables I would need to query would be slightly different. No problem. I’ve written before about how I like to write my scripts to be version agnostic. Well, they aren’t truly agnostic in that they don’t care about which version they are running on, but they do detect the version and then use the appropriate tables. By writing scripts this way, I can execute the same script in SSMS against multiple servers at once and not get errors.

Below is my code to count all user tables, views, and stored procedures in all user databases on a server. Note that is skips system databases and offline databases. If you want to count things besides tables, views, and stored procedures, simply add them to the CASE and WHERE statements in the definition of the @SQL variable. This script will work on 2000, 2005, 2008, and 2008 R2 servers. It will probably even work on SQL 7.0 servers, but you’ll need to change the IF statement that looks at the ProductVersion property.

 

/*
Script to count all user tables, views, and stored procedures in
	all user databases on a server.

shaunjstuart.com

Verion 1.0

*/

DECLARE @DBName AS VARCHAR(100)
DECLARE @TotalUserTables INT
DECLARE @TotalStoredProcedures INT
DECLARE @TotalViews INT

CREATE TABLE #tblDBResults
        (
         DatabaseName VARCHAR(100)
        ,Item VARCHAR(50)
        ,Total_count INT
        )

DECLARE @SQL VARCHAR(1000)
	/* basic select statement */
DECLARE @SQLWithDB VARCHAR(1000)
	/* select statement with USE db added */

SET @TotalUserTables = 0
SET @TotalStoredProcedures = 0
SET @TotalViews = 0

/* Use appropriate tables based on SQL Server version */
/* 8 = SQL 2000, otherwise assume 2005+ */

IF (SELECT  LEFT(CAST(SERVERPROPERTY('ProductVersion') AS CHAR(1)), 1)
   ) = 8
   BEGIN

-- Configure basic select statement (without 'USE db' part) for SQL 2000 servers

         SET @SQL = 'SELECT	DB_NAME(),
							[type]
						   ,total_count = COUNT(*)
					FROM    (SELECT type = CASE WHEN xtype = ''U'' THEN ''User tables''
												WHEN xtype = ''V'' THEN ''Views''
												WHEN xtype = ''P'' THEN ''Stored procs''
										   END
							 FROM   sysobjects
							 WHERE  xtype IN (''P'', ''U'', ''V'')
							) s
					GROUP BY type;'

         DECLARE DatabasesCursor CURSOR
         FOR
                 SELECT name
                 FROM   sysdatabases
                 WHERE  dbid > 4
                        AND (512 & status) <> 512
					/* exclude system and offline databases */
   END
ELSE
   BEGIN

-- Configure basic select statement (without 'USE db' part) for SQL 2005+ servers

         SET @SQL = 'SELECT	DB_NAME(),
					[type]
                   ,total_count = COUNT(*)
            FROM    (SELECT type = CASE WHEN type = ''U'' THEN ''User tables''
                                        WHEN type = ''V'' THEN ''Views''
                                        WHEN type = ''P'' THEN ''Stored procs''
                                   END
                     FROM   sys.objects
                     WHERE  type IN (''P'', ''U'', ''V'')
                    ) s
            GROUP BY type;'

         DECLARE DatabasesCursor CURSOR
         FOR
                 SELECT name
                 FROM   sys.databases
                 WHERE  Database_id > 4
                        AND sys.databases.state = 0
				/* exclude system and offline databases */
   END

OPEN DatabasesCursor
FETCH NEXT FROM DatabasesCursor INTO @DBName

WHILE @@fetch_status = 0
      BEGIN

            SET @SQLWithDB = 'USE [' + @DBName + '];' + CHAR(10) + CHAR(13)
                + @SQL

            INSERT  #tblDBResults
                    EXEC (@SQLWithDB)

            FETCH NEXT FROM DatabasesCursor INTO @DBName

      END

CLOSE DatabasesCursor
DEALLOCATE DatabasesCursor

SELECT  Item
       ,SUM(Total_count) AS TotalItems
FROM    #tblDBResults
GROUP BY [#tblDBResults].Item
ORDER BY [#tblDBResults].Item

DROP TABLE #tblDBResults

Unfortunately, if you are running this across multiple servers using the SSMS trick I mentioned earlier, there is one additional step you need to do. SSMS, when performing a query against multiple servers, will add a column to the result set called Server Name. Your result set will include a set of numbers for each server. There is no way to have SSMS aggregate these into one set of totals. You’ll need to copy and paste the results into Excel and sum up the numbers of tables, views, and stored procedures across all servers.

By the way, counted this way, my count of database objects went from just over 7,000 to over 550,000, even while the number of databases dropped by 150.  Restricting the count to only user tables, the number was still over 250,000. Clearly the old value was looking at something else. But since no one knows what it was, I’m going to redefine the term and do things my way.