How to findout that how many objects(Type wise) are there in all databases in server ?

Posted by PandianS under Sql Server category on | Points: 40 | Views : 2136
How to findout object count type wise (User Table, Procedure, Function, Trigger, Constraints,...Etc) in all the databases in Server ?
Use Master

CREATE TABLE #ObjectHistory
[Database Name] VARCHAR(100),
[Object Type] VARCHAR(100),
[No. of Objects] INT

INSERT #ObjectHistory
EXEC('SP_MsForEachDB ''SELECT ''''?'''',[Type_Desc],COUNT(1) FROM ?.Sys.Objects
WHERE Is_MS_Shipped = 0
GROUP BY [Type_Desc]''')

SELECT * FROM #ObjectHistory ORDER BY [Database Name], [Object Type]

DROP TABLE #ObjectHistory

Comments or Responses

Login to post response