How to Count total number of objects in SQL Server instance

Below query will help you find the list of object counts in the database:

SELECT
type,
total_count = COUNT(*),
last_crdate = MAX(crdate),
last_refdate = MAX(refdate)
FROM (
SELECT
type = CASE
WHEN xtype = ‘U’ THEN ‘table’
WHEN xtype = ‘V’ THEN ‘view’
WHEN xtype = ‘P’ THEN ‘proc’
WHEN xtype IN (‘FN’, ‘TF’, ‘IF’) THEN ‘fuction’
END,
crdate,
refdate
FROM sysobjects
WHERE xtype IN (‘FN’, ‘TF’, ‘IF’, ‘P’, ‘U’, ‘V’)
) s
GROUP BY type

Be Connected…

Satyendra

Leave a Reply

Your email address will not be published. Required fields are marked *