iT邦幫忙

3

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 = 1
set @Search = '林志玲'
set @Cmd = ''

insert into @Tmp
select Row_Number() over(order by dbs_name,table_name,columns_name) as Sort
,dbs_name,table_name,columns_name
from ((
	select 'A資料庫' as dbs_name
	,a.name as table_name
	,b.name as columns_name
	,d.name as types_name
	from A資料庫.dbo.sysobjects as a
	left join A資料庫.dbo.syscolumns as b on a.id = b.id
	left join A資料庫.dbo.systypes as d on b.xusertype = d.xusertype
	where 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_name
	from B資料庫.dbo.sysobjects as a
	left join B資料庫.dbo.syscolumns as b on a.id = b.id
	left join B資料庫.dbo.systypes as d on b.xusertype = d.xusertype
	where a.xtype = 'U'
	and a.name <> 'dtproperties'
	and d.name in('varchar','nvarchar')
)) as k

select @Count = count(*)
from @Tmp

if @Count > 0
begin
	set @Cmd = 'select * from (('
	while(@i <= @Count)
	begin
		select @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 @Tmp
		where sort = @i
		set @i += 1
	end
	set @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 = 1
set @Search = '林志玲'

再來要查詢的資料庫資料,存到暫存資料表(請記得你要有所有資料表的權限@@..否則會報錯)
這裡我有指定欄位需要是varchar、nvarchar,當然你也可以加其他類型欄位例如char

insert into @Tmp
select Row_Number() over(order by dbs_name,table_name,columns_name) as Sort
,dbs_name,table_name,columns_name
from ((
	select 'A資料庫' as dbs_name
	,a.name as table_name
	,b.name as columns_name
	,d.name as types_name
	from A資料庫.dbo.sysobjects as a
	left join A資料庫.dbo.syscolumns as b on a.id = b.id
	left join A資料庫.dbo.systypes as d on b.xusertype = d.xusertype
	where 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_name
	from B資料庫.dbo.sysobjects as a
	left join B資料庫.dbo.syscolumns as b on a.id = b.id
	left join B資料庫.dbo.systypes as d on b.xusertype = d.xusertype
	where a.xtype = 'U'
	and a.name <> 'dtproperties'
	and d.name in('varchar','nvarchar')
)) as k

最後透過TSQL處理方式,將所在資料列出來~~就知道你想要的字串在哪裡~

if @Count > 0
begin
	set @Cmd = 'select * from (('
	while(@i <= @Count)
	begin
		select @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 @Tmp
		where sort = @i
		set @i += 1
	end
	set @Cmd = @Cmd + ')) as k'
	exec(@Cmd)
end

最後我有試過這方式@@...好像查詢時間花更久..不知道為什麼比用迴圈的久..(還是我錯覺= =a)

if @Count > 0
begin
	set @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 @Tmp
	set @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;


1 則留言

1
神Q超人
iT邦新手 2 級 ‧ 2018-07-05 22:10:21

我也有看到這個問題XD
因為看到留言大大說MS有方法可以,
就讓我一直很期待,沒想到居然是開在文章中/images/emoticon/emoticon42.gif

哈~以前為了某區公所(公司維護)無法遠端連線MSSQL~(那時候是2000版)
想出來的網頁版的資料庫系統線上管理@@~

不然每次都要賭運氣去某區公所才能更新欄位...

我要留言

立即登入留言