single procedure against multiple databases

Posted on 2004-11-26
Last Modified: 2008-03-10
I want to create a single procedure in master that will be called within a loop in each database of my server.
The goal is to count objects of each of my 500 databases and to report them.

It works when I launch the dynamic query while connected in a database, but it returns the report for master when I exec the proc.

use mydb
exec master..myproc

returns number of objects by type for the master database.

Stored procedure that I want to exec in each of my 500 databases:

create proc myproc

set nocount on
print 'Server : ' + @@servername
print 'Database : ' + db_name()
print ''
print '********************************************************************************************'
print 'Object number by Type for database : ' + db_name()
print '********************************************************************************************'
print ''

select substring(,5,31) as 'Type d''objet', count(o.xtype) as 'Nombre d''objets'
from sysobjects o, master.dbo.spt_values v
where o.xtype = substring(,1,2) collate database_default and v.type = 'O9T'
group by
order by


Question by:fho
    1 Comment
    LVL 6

    Accepted Solution

    set nocount on

    Declare @SQLStr varchar(4000)
    Declare @Databases table (Name varchar(60),MyId int identity(1,1))
    Declare @Current int
    Declare @Max int
    declare @NAme varchar(60)
    insert into @Databases Select name  from master.dbo.sysdatabases
    Select @Current=min(MYId),@Max=max(MyId) from @Databases
    While @Current<=@Max Begin
    select @Name=Name from @Databases where MyId=@Current
    print 'Server : ' + @@servername
    print 'Database : ' + @NAme
    print ''
    print '********************************************************************************************'
    print 'Object number by Type for database : ' + @NAme
    print '********************************************************************************************'
    print ''

    Set @SQLStr='select substring(,5,31) as ''Type d''''objet'', count(o.xtype) as ''Nombre d''''objets''
    from '+@Name+'.dbo.sysobjects o, master.dbo.spt_values v
    where o.xtype = substring(,1,2) collate database_default and v.type = ''O9T''
    group by
    order by

    exec( @SQLStr)
    set @Current=@Current+1

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now