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
which system database do you run this script against.
ReplyDeleteMicrosoft Sql server
ReplyDelete