March 24, 2005 at 4:43 pm
does anyone have a handy script to get a count of all user objects (tables, views, stored procedures, etc.) from every database on a sql server ... preferably without running it in each database individually?
March 25, 2005 at 4:23 am
Undocumented sp_msforeachdb stored procedure can help it.
Count user table for each db in the instance.
declare @sql varchar(400)
set @sql='select count(*) from ?.dbo.sysobjects where xtype = ''u'''
exec sp_msforeachdb @sql
March 27, 2005 at 3:41 pm
You can use sp_msforeachdb as wz700 as suggested, but the query itself should be modified to something like:
select count(*) as [?]
from [?].dbo.sysobjects
where OBJECTPROPERTY(id, 'IsMSShipped') = 0
That way you count stored procs, views, functions, etc and you can label the count with the database name.
Scott Thornburg
March 28, 2005 at 2:42 pm
thank you very much!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply