Script of the day: Count all objects in a database

Script of the day: Count all objects in a database

Rate This
  • Comments 5

This script counts all of your objects in the database, at least the ones stored in the sysobjects table. Some objects (such as jobs) don't show up there - one day I'll add those:

/*
usc_DBA_Count_Objects.sql
Author: Buck Woody
Purpose: Shows a quick count of database objects found in sysobjects */
SELECT 'Count' COUNT(*), 'Type' CASE 
type
                
WHEN 'C' THEN 
'CHECK constraints'
                
WHEN 'D' THEN 
'Default or DEFAULT constraints'
                
WHEN 'F' THEN 
'FOREIGN KEY constraints'
                
WHEN 'FN' THEN 
'Scalar functions'
                
WHEN 'IF' THEN 
'Inlined table-functions'
                
WHEN 'K' THEN 
'PRIMARY KEY or UNIQUE constraints'
                
WHEN 'L' THEN 
'Logs'
                
WHEN 'P' THEN 
'Stored procedures'
                
WHEN 'R' THEN 
'Rules'
                
WHEN 'RF' THEN 
'Replication filter stored procedures'
                
WHEN 'S' THEN 
'System tables'
                
WHEN 'TF' THEN 
'Table functions'
                
WHEN 'TR' THEN 
'Triggers'
                
WHEN 'U' THEN 
'User tables'
                
WHEN 'V' THEN 
'Views'
                
WHEN 'X' THEN 
'Extended stored procedures'
    
END
            
GETDATE
()
    
FROM 
sysobjects
    
GROUP BY 
type
    
ORDER BY 
type
GO

Leave a Comment
  • Please add 3 and 7 and type the answer here:
  • Post
  • PingBack from http://www.artofbam.com/wordpress/?p=2437

  • Is it just a style preference, or is there anything gained from naming columns how you're doing it vs the following:

    SELECT Count(*) 'Count'.

  • It's just a style thing. I go way back with the SQL language, so I tend to use more of the formalities with it. Good question, though!

  • Awesome script. Just what I was looking for. Thanks for posting it. You da MAN!

  • Great Script.  Thanks for sharing. :-)

Page 1 of 1 (5 items)