Query to findout list of Objects count from all databases in SQL Server :

This is very useful query on DBA daily work environment. I have struggled to findout the information before. Finally I developed following query to get the information.

Select 'select ('''+name+''') Database_name,
(Select count(*) from '+name+'.dbo.sysobjects where xtype = ''u'') Table_Count,
(Select count(*) from '+name+'.dbo.sysobjects where xtype = ''v'') View_Count,
(Select count(*) from '+name+'.dbo.sysobjects where xtype in (''fn'', ''tn'')) func_Count,
(Select count(*) from '+name+'.dbo.sysobjects where xtype = ''p'') proc_Count,
(Select count(*) from '+name+'.dbo.sysobjects where xtype = ''tr'') Trig_Count union all'
from sysdatabases where dbid > 4


Instructions :

* you need to take the result script of above query and execute in other window. And you need to remove 'UNION ALL" from end statement.
* dbid > 4 = it will not show for sysdatabases



Result Query :

select ('TEST') Database_name, (Select count(*) from TEST.dbo.sysobjects where xtype = 'u') Table_Count, (Select count(*) from TEST.dbo.sysobjects where xtype = 'v') View_Count, (Select count(*) from TEST.dbo.sysobjects where xtype in ('fn', 'tn')) func_Count, (Select count(*) from TEST.dbo.sysobjects where xtype = 'p') proc_Count, (Select count(*) from TEST.dbo.sysobjects where xtype = 'tr') Trig_Count