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.