• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

single procedure against multiple databases

Hi
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
as

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(v.name,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(v.name,1,2) collate database_default and v.type = 'O9T'
group by v.name
order by v.name


TIA

Fred
0
fho
Asked:
fho
1 Solution
 
acampomaCommented:
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(v.name,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(v.name,1,2) collate database_default and v.type = ''O9T''
group by v.name
order by v.name
'

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now