Thursday, February 10, 2011

Finding the database to which a stored procedure belongs

The stored procedure is just to find the database to which a stored procedure belongs to. This is very useful if there are lot of databases. The user may confused to find database which has the required stored procedure. This is small program but proves to be a handy one.

alter procedure dbnames
as
begin

declare @names varchar(256)
declare @dbname varchar(32)
declare @sql varchar(8000)

declare dbnames cursor
for
select name from master.dbo.sysdatabases
open dbnames
fetch next from dbnames into @names
while (@@FETCH_STATUS=0)
begin
set @sql = 'select convert(varchar,specific_catalog,32) from '+@names+'.INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME =''account_modelSNameupdate'''
exec (@sql)
if (@@rowcount>0)
begin
select @dbname
end
fetch next from dbnames into @names
end
close dbnames
deallocate dbnames
end

exec dbnames

2 comments: