June 25, 2008 at 2:43 pm
I have about 20 databases on one instance of SQL server, stored procedures get deployed to all but sometimes only selected databases, how can I search for a SP and see in which databases it resides.
Thank you.
June 25, 2008 at 11:40 pm
I have a procedure that you can use for this and can be used for finding tables or views also.
I hope will help you
example: exec spSearchObject '%SearchObject%', '%','P'
create procedure [spSearchObject]
@ObjectName sysname, --name of the object to be searched, can be an expression also
@DatabaseList nvarchar(max) = '%',
@XType varchar(max) = 'U,%F%,P,V',
@SkipDatabases nvarchar(max) = 'master,msdb,model,tempdb',
@IncludeSysObjects bit = 0
as
begin
set nocount on
declare @sql varchar(max), @db_name sysname, @ob sysname, @tmp_table_name sysname
set @tmp_table_name='##'+replace(cast(newid() as varchar(max)),'-','')
set @sql='create table ['+@tmp_table_name+'] (
[db_name] sysname not null,
[schema_name] sysname not null,
[ob_name] sysname not null,
[xtype] sysname not null,
[object_id] int not null)'
exec (@sql)
--split included databases
declare @included_databases table ([db_name] sysname)
if @DatabaseList is not null
begin
while charindex(',',@DatabaseList)>0
begin
insert into @included_databases ([db_name])
values(left(@DatabaseList,charindex(',',@DatabaseList)-1))
set @DatabaseList=right(@DatabaseList, len(@DatabaseList)- charindex(',',@DatabaseList))
end
insert into @included_databases ([db_name]) values(@DatabaseList)
end
--split excluded databases
declare @excluded_databases table ([db_name] sysname)
if @SkipDatabases is not null
begin
while charindex(',',@SkipDatabases)>0
begin
insert into @excluded_databases ([db_name])
values(left(@SkipDatabases,charindex(',',@SkipDatabases)-1))
set @SkipDatabases=right(@SkipDatabases, len(@SkipDatabases)- charindex(',',@SkipDatabases))
end
insert into @excluded_databases ([db_name]) values(@SkipDatabases)
end
--split xtype
set @sql='create table ['+@tmp_table_name+'_xtypes] ([xtype] varchar(max))'
exec (@sql)
while charindex(',',@XType)>0
begin
set @sql='insert into ['+@tmp_table_name+'_xtypes] ([xtype]) values('''+left(@XType,charindex(',',@XType)-1)+''')'
exec(@sql)
set @XType=right(@XType, len(@XType)- charindex(',',@XType))
end
set @sql='insert into ['+@tmp_table_name+'_xtypes] ([xtype]) values('''+@XType+''')'
exec(@sql)
--find objects
declare cr cursor for
select distinct d.name
from master..sysdatabases d
inner join @included_databases i on d.name like i.[db_name]
left outer join @excluded_databases e on d.name like e.[db_name]
where ((status/128)&1) = 0 and e.[db_name] is null
order by name asc
open cr
fetch next from cr into @db_name
while @@fetch_status=0
begin
begin try
set @sql='
insert into ['+@tmp_table_name+'] ([db_name],[schema_name],[ob_name],[xtype],[object_id])
select
'''+@db_name+''' as db_name,
s.name as schema_name,
o.name as ob_name,
o.type as xtype,
o.object_id as object_id
from ['+@db_name+'].sys.objects o
inner join ['+@db_name+'].sys.schemas s on o.schema_id = s.schema_id
inner join ['+@tmp_table_name+'_xtypes] t on CAST(ltrim(rtrim(o.type)) AS sysname) collate SQL_Latin1_General_CP1_CI_AS like t.xtype
where o.name like '''+@ObjectName+''' and (o.type<>''S'' or '+str(@IncludeSysObjects)+'=1)
order by o.name'
exec (@sql)
end try
begin catch
print 'database '+@db_name+' skiped'
end catch
fetch next from cr into @db_name
end
close cr
deallocate cr
--output results
set @sql='select * from ['+@tmp_table_name+']'
exec (@sql)
--droping temporary tables
set @sql='drop table ['+@tmp_table_name+']'
exec (@sql)
set @sql='drop table ['+@tmp_table_name+'_xtypes]'
exec (@sql)
end
go
June 26, 2008 at 12:49 pm
This is great!!!
Thank you!!!:cool:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply