Script of the day: Count all objects in a database


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

Comments (5)

  1. darylchance says:

    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’.

  2. BuckWoody says:

    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!

  3. Sean says:

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

  4. Joe Hayes says:

    Great Script.  Thanks for sharing. 🙂

Skip to main content