Quick count on all SQL Server Object Types

How many times have you copied objects from database A to B, and needed to compare object counts when complete?  Or, maybe you just want to see what's in one database that is not in another?  Here's a quick piece to give you object type counts in your database, and another to show you what's in one database that's not in the other.

-- object type counts

SELECT

       'Count' = COUNT(*),

       s.Type,

       'Description' = CASE [Type]

       WHEN 'AF' THEN 'Aggregate function (CLR)'

       WHEN 'C' THEN 'CHECK Constraint'

       WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)'

       WHEN 'EC' THEN 'Edge Constraint'

       WHEN 'F' THEN 'FOREIGN KEY Constraint'

       WHEN 'FN' THEN 'Scalar functions'

       WHEN 'IF' THEN 'SQL Inline Table-valued Function'

       WHEN 'IT' THEN 'Internal table'

       WHEN 'P' THEN 'SQL Stored Procedure'

       WHEN 'PK' THEN 'Primary Key'

       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 'UQ' THEN 'UNIQUE Constraint'

       WHEN 'U' THEN 'User Table'

       WHEN 'V' THEN 'View'

       WHEN 'X' THEN 'Extended stored procedure'

       ELSE type END

FROM

       sys.objects s

GROUP BY

       s.type

ORDER BY

       s.type

Here's the output from my AdventureWorks2012 database:           

                

And this piece will tell you what tables are in the database you are connected to, that is not in the 'DatabaeName' on the other 'ServerName' in the query:  

SELECT a.name FROM sys.objects a

WHERE a.type = 'U'

AND NOT EXISTS(

       SELECT 1 FROM ServerName.DatabaseName.sys.objects b

       WHERE b.type = 'U'

       AND a.name = b.name

       )

Of course, you can change it to any object type, or even all objects... and you'll need to have a linked server for 'ServerName' in order for it to work.

Hope it helps!