MSSQL如何跨库多表查询某个关键字在某个栏位

恩..

其实这文章是来自此问答而来的
请问如何在 MySQL 资料库中找到某字串？

如果是用MSSQL方式查询的话~

可以用此方式来带出所在资料

declare @Tmp table(sort int,dbs_name nvarchar(50),table_name nvarchar(50),columns_name nvarchar(50))declare @Count int,@i int,@Cmd nvarchar(max),@Search nvarchar(50)set @i = 1set @Search = '林志玲'set @Cmd = ''insert into @Tmpselect Row_Number() over(order by dbs_name,table_name,columns_name) as Sort,dbs_name,table_name,columns_namefrom ((select 'A资料库' as dbs_name,a.name as table_name,b.name as columns_name,d.name as types_namefrom A资料库.dbo.sysobjects as aleft join A资料库.dbo.syscolumns as b on a.id = b.idleft join A资料库.dbo.systypes as d on b.xusertype = d.xusertypewhere a.xtype = 'U'and a.name <> 'dtproperties'and d.name in('varchar','nvarchar'))union all(select 'B资料库' as dbs_name,a.name as table_name,b.name as columns_name,d.name as types_namefrom B资料库.dbo.sysobjects as aleft join B资料库.dbo.syscolumns as b on a.id = b.idleft join B资料库.dbo.systypes as d on b.xusertype = d.xusertypewhere a.xtype = 'U'and a.name <> 'dtproperties'and d.name in('varchar','nvarchar'))) as kselect @Count = count(*)from @Tmpif @Count > 0beginset @Cmd = 'select * from (('while(@i <= @Count)beginselect @Cmd = @Cmd + (case when @i > 1 then ')union all(' else '' end) + ' select ''' + dbs_name + ''' as dbs_name,''' + table_name + ''' as table_name,''' + columns_name + ''' as columns_name,''' + @Search + ''' as Search_Txt,count(*) as Num from ' + dbs_name + '.dbo.' + table_name + ' where ' + columns_name + ' like N''%' + @Search + '%'' having count(*) > 0 'from @Tmpwhere sort = @iset @i += 1endset @Cmd = @Cmd + ')) as k'exec(@Cmd)end

首先要先宣告一些TSQL的资料变数定义

declare @Tmp table(sort int,dbs_name nvarchar(50),table_name nvarchar(50),columns_name nvarchar(50))declare @Count int,@i int,@Cmd nvarchar(max),@Search nvarchar(50)

然后再给他一些初始值设定，例如要搜索的字串

set @i = 1set @Search = '林志玲'

再来要查询的资料库资料，存到暂存资料表(请记得你要有所有资料表的权限@@..否则会报错)
这里我有指定栏位需要是varchar、nvarchar，当然你也可以加其他类型栏位例如char

insert into @Tmpselect Row_Number() over(order by dbs_name,table_name,columns_name) as Sort,dbs_name,table_name,columns_namefrom ((select 'A资料库' as dbs_name,a.name as table_name,b.name as columns_name,d.name as types_namefrom A资料库.dbo.sysobjects as aleft join A资料库.dbo.syscolumns as b on a.id = b.idleft join A资料库.dbo.systypes as d on b.xusertype = d.xusertypewhere a.xtype = 'U'and a.name <> 'dtproperties'and d.name in('varchar','nvarchar'))union all(select 'B资料库' as dbs_name,a.name as table_name,b.name as columns_name,d.name as types_namefrom B资料库.dbo.sysobjects as aleft join B资料库.dbo.syscolumns as b on a.id = b.idleft join B资料库.dbo.systypes as d on b.xusertype = d.xusertypewhere a.xtype = 'U'and a.name <> 'dtproperties'and d.name in('varchar','nvarchar'))) as k

最后透过TSQL处理方式，将所在资料列出来~~就知道你想要的字串在哪里~

if @Count > 0beginset @Cmd = 'select * from (('while(@i <= @Count)beginselect @Cmd = @Cmd + (case when @i > 1 then ')union all(' else '' end) + ' select ''' + dbs_name + ''' as dbs_name,''' + table_name + ''' as table_name,''' + columns_name + ''' as columns_name,''' + @Search + ''' as Search_Txt,count(*) as Num from ' + dbs_name + '.dbo.' + table_name + ' where ' + columns_name + ' like N''%' + @Search + '%'' having count(*) > 0 'from @Tmpwhere sort = @iset @i += 1endset @Cmd = @Cmd + ')) as k'exec(@Cmd)end

最后我有试过这方式@@...好像查询时间花更久..不知道为什么比用迴圈的久..(还是我错觉= =a)

if @Count > 0beginset @Cmd = 'select * from (('select @Cmd = @Cmd + (case when sort > 1 then ')union all(' else '' end) + ' select ''' + dbs_name + ''' as dbs_name,''' + table_name + ''' as table_name,''' + columns_name + ''' as columns_name,''' + @Search + ''' as Search_Txt,count(*) as Num from ' + dbs_name + '.dbo.' + table_name + ' where ' + columns_name + ' like N''%' + @Search + '%'' having count(*) > 0 'from @Tmpset @Cmd = @Cmd + ')) as k'exec(@Cmd)end

补充：(218/7/6)
后来查到这句语法

MSSQL可以这样做
SELECT * FROM A资料库.information_schema.columns

MySQL要这样下才可以@@
SELECT * FROM information_schema.columns
where table_schema = 'A资料库';

SET @Sql = 'SQL语句';
PREPARE test from @Sql;
execute test;