Post reply

count all user objects in all databases

  • March 24, 2005 at 4:43 pm

    #67179

    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

    #548920

    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

    #549070

    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

    #549201

    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