T-SQL: Count of Tables, Stored Procedures, and Functions in All User Databases

This query uses the undocumented Microsoft stored procedure master.sys.sp_MSforeachdb to iterate over all user databases (user databases always start with IDs > 4) and count the number of tables, stored procedures and functions. You can use this same pattern to count other object types as well.

EXECUTE master.sys.sp_MSforeachdb '
IF (DB_ID(''?'') > 4)
BEGIN
  SELECT 
    ''?'' As DatabaseName, 
    CASE xtype
      WHEN ''u'' THEN ''Table''
      WHEN ''p'' Then ''Procedure''
      WHEN ''fn'' THEN ''Function''
    END
    As Type, 
    Count(*) As Count 
  FROM ?.sys.sysobjects 
  WHERE 
    xtype IN (''u'', ''p'', ''fn'')  
  GROUP BY xtype
END
'

Hope this helps!

This entry was posted in T-SQL. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *