Count all objects in a database

Standard

Count von Count
Problem
I want to count all the objects in a particular database and with a good description of the type groups by the type.

Solution
I love Google and people are amazing with all the solutions they already offer. I found a script by Buck Woody which created a nice solution.
I adjusted the script by adding more types to the script and convert the date to an easier timestamp.

SELECT 'Count' = Count(*), 
       'Type' = CASE type 
                  WHEN 'AF' THEN 'Aggregate function (CLR)' 
                  WHEN 'C' THEN 'CHECK constraint' 
                  WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)' 
                  WHEN 'F' THEN 'FOREIGN KEY constraint' 
                  WHEN 'FN' THEN 'SQL scalar function' 
                  WHEN 'FS' THEN 'Assembly (CLR) scalar-function' 
                  WHEN 'FT' THEN 'Assembly (CLR) table-valued function' 
                  WHEN 'IF' THEN 'SQL inline table-valued function' 
                  WHEN 'IT' THEN 'Internal table' 
                  WHEN 'P' THEN 'SQL Stored Procedure' 
                  WHEN 'PC' THEN 'Assembly (CLR) stored-procedure' 
                  WHEN 'PG' THEN 'Plan guide' 
                  WHEN 'PK' THEN 'PRIMARY KEY constraint' 
                  WHEN 'R' THEN 'Rule (old-style, stand-alone)' 
                  WHEN 'RF' THEN 'Replication-filter-procedure' 
                  WHEN 'S' THEN 'System base table' 
                  WHEN 'SN' THEN 'Synonym' 
                  WHEN 'SO' THEN 'Sequence object' 
                  WHEN 'SQ' THEN 'Service queue' 
                  WHEN 'TA' THEN 'Assembly (CLR) DML trigger' 
                  WHEN 'TF' THEN 'SQL table-valued-function' 
                  WHEN 'TR' THEN 'SQL DML trigger' 
                  WHEN 'TT' THEN 'Table type' 
                  WHEN 'U' THEN 'Table (user-defined)' 
                  WHEN 'UQ' THEN 'UNIQUE constraint' 
                  WHEN 'V' THEN 'View' 
                  WHEN 'X' THEN 'Extended stored procedure' 
                END, 
       CONVERT(VARCHAR(19), Getdate(), 120) AS 'Timestamp' 
FROM   sysobjects 
GROUP  BY type 
ORDER  BY type 

GO

Hope you enjoy the script.

Leave a Reply

Your email address will not be published.