根据表名、过程名、视图名查找对应的数据库

/****************************************
*根据表名、过程名、视图名查找对应的数据库
*
*例：sp_find_databasename 'SP_BBSList_Select'
*
*****************************************/
alter proc sp_find_databasename
@findname nvarchar(200) = '' --表名、过程名、视图名
as
declare @database_name nvarchar(200)
declare @table table(name nvarchar(200)) 
declare @sql nvarchar(1000)
declare @n int 

if @findname = ''
return 

declare findname cursor for select name from sys.databases --游标取所有数据库名
open findname
FETCH NEXT FROM findname INTO @database_name

WHILE (@@FETCH_STATUS = 0)
begin
set @sql = N'select @total = count(1) from '+@database_name + '.dbo.sysobjects where name = '''+ @findname +'''' 
print @sql
exec  sp_executesql @sql,N'@total int output',@n output 
if @n > 0
begin
insert into @table values (@database_name)
end
FETCH NEXT FROM findname INTO @database_name
end

CLOSE findname
DEALLOCATE findname

select * from @table

遇到一个蛋疼的问题，刚开始变量名是varchar类型的一直报错，后来查看帮助文档才知道必需要nvarchar类型的

转载于:https://www.cnblogs.com/jonhson/archive/2011/10/10/2205831.html