透過 PowerShell 讀 MSMQ,以及在資料庫內,跨資料表與欄位找尋是否包含某個字串

朋友的需求:

$q= new-object System.Messaging.MessageQueue ‘機器名稱\private$\erptowf’
$msg=$q.Peek()

$s=new-object System.IO.StreamReader $msg.BodyStream

$s.ReadToEnd()

 

declare cur cursor
for select schema_name(o.schema_id) schName, object_name(c.object_id) tbName,c.name from sys.columns c
join sys.objects o on c.object_id=o.object_id
where TYPE_NAME(user_type_id) in (‘varchar’,’nvarchar’,’char’,’nchar’) and o.type=’u’ and o.is_ms_shipped=0

declare @sch sysname,@tb sysname,@col sysname
create table t(schemaname sysname,tablename sysname,columnname sysname)
open cur
fetch cur into @sch,@tb,@col
declare @sql nvarchar(max), @search nvarchar(100)=’58.’
while @@FETCH_STATUS =0
begin
    set @sql=’if exists(select * from [‘ + @sch + ‘].[‘ + @tb + ‘] where [‘ + @col + ‘] like “%’ + @search + ‘%") insert t values(“‘ +@sch + “‘,"‘ + @tb + “‘,"‘ + @col + “‘)’
    –print @sql
    exec(@sql)
    fetch cur into @sch,@tb,@col
end
close cur
deallocate cur
select * from t
drop table t

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: