CREATE VIEW dbo.DBA_GetObjectBreakdown
AS
/*
Author: Scott Whigham from
http://www.LearnSqlServer.com/ Description: This returns a high-level overview of the database in the form of the number of objects
of each type and the percentage of the database objects
Misc Notes: Refer to Books Online for full listing:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/f8d6163a-2474-410c-a794-997639f31b3b.htm
Versions: SQL Server 2005
Creation Date: August 29, 2006
For more scripts like this one, visit
http://forums.learnsqlserver.com/codesamples.aspx*/
SELECT
ISNULL ( CASE type
WHEN 'U' THEN 'Tables'
WHEN 'V' THEN 'Views'
WHEN 'P' THEN 'Stored Procedures'
WHEN 'FN' THEN 'Scalar functions'
WHEN 'IF' THEN 'Inline functions'
WHEN 'TF' THEN 'Table-valued functions'
WHEN 'PK' THEN 'Primary Keys'
WHEN 'F' THEN 'Foreign keys'
WHEN 'C' THEN 'Check constraints'
WHEN 'UQ' THEN 'Unique constraints'
WHEN 'D' THEN 'Default Constraints'
WHEN 'TR' THEN 'Triggers'
WHEN 'TA' THEN 'CLR - DML Triggers'
WHEN 'AF' THEN 'CLR - Aggregate Functions'
WHEN 'PC' THEN 'CLR - Stored Procedures'
WHEN 'FS' THEN 'CLR - Scalar Functions'
WHEN 'FT' THEN 'CLR - Table-Valued Functions'
WHEN 'SN' THEN 'Synonyms'
WHEN 'X' THEN 'Extended Stored Procedures'
WHEN 'SQ' THEN 'Service Queues'
WHEN 'IT' THEN 'Internal Tables'
ELSE type
END, '*** All Objects ***' ) AS ObjectType
, COUNT(*) AS TotalNumberOfObjects
, CAST ( 100 *
CAST(COUNT(*) AS FLOAT) / ( SELECT COUNT(*) FROM sys.objects WHERE type NOT IN ('S')AND is_ms_shipped = 0 )
AS DECIMAL(5, 2) ) AS Percentage
FROM sys.objects
WHERE type NOT IN ('S')AND is_ms_shipped = 0
GROUP BY type WITH ROLLUP
GO
SELECT * FROM DBA_GetObjectBreakdown
