恩..
其實這文章是來自此問答而來的
請問如何在 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;
我也有看到這個問題XD
因為看到留言大大說MS有方法可以,
就讓我一直很期待,沒想到居然是開在文章中
哈~以前為了某區公所(公司維護)無法遠端連線MSSQL~(那時候是2000版)
想出來的網頁版的資料庫系統線上管理@@~
不然每次都要賭運氣去某區公所才能更新欄位...