{"id":483,"date":"2017-01-06T22:45:08","date_gmt":"2017-01-06T13:45:08","guid":{"rendered":"http:\/\/www.onepage.co.kr\/wordpress\/?p=483"},"modified":"2017-01-06T22:45:08","modified_gmt":"2017-01-06T13:45:08","slug":"finding-specific-text-string-in-column-through-all-tables-procedure","status":"publish","type":"post","link":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/2017\/01\/06\/finding-specific-text-string-in-column-through-all-tables-procedure\/","title":{"rendered":"finding specific text string in column  through all tables &#8211; procedure"},"content":{"rendered":"<p>declare @name sysname, @id int<br \/>\ndeclare @sname sysname,@xtype int<br \/>\ndeclare @sql varchar(1000) , @b_data varchar(1000), @a_data varchar(10)<br \/>\nset @b_data = &#8216;http:\/\/&#8217;<\/p>\n<p>DECLARE MYCUR CURSOR FOR<\/p>\n<p>select name,id from dbo.sysobjects where xtype=&#8217;U&#8217;<\/p>\n<p>OPEN\u00a0 MYCUR<br \/>\nFETCH NEXT FROM MYCUR INTO @name, @id<br \/>\nWHILE (@@FETCH_STATUS = 0)<br \/>\nBEGIN<br \/>\nDECLARE subcur CURSOR FOR<br \/>\nselect name,xtype from dbo.syscolumns<br \/>\nwhere xtype in (<br \/>\n167 &#8212; varchar<br \/>\n,175 &#8212; char<br \/>\n,231 &#8211;nvarchar<br \/>\n,239 &#8211;nchar<br \/>\n)<br \/>\nand id=@id<\/p>\n<p>OPEN subcur<br \/>\nFETCH NEXT FROM subcur INTO @sname,@xtype<br \/>\nWHILE (@@FETCH_STATUS = 0)<br \/>\nBEGIN<\/p>\n<p>set @sql = &#8216;select * from dbo.&#8217;+@name+&#8217; where &#8216;+ @sname + &#8216; like &#8221;%&#8217;+ @b_data +&#8217;%&#8221;&#8217;<br \/>\nexec (@sql)<br \/>\nFETCH NEXT FROM subcur INTO @sname,@xtype<br \/>\nEND<br \/>\nCLOSE\u00a0 subcur<br \/>\nDEALLOCATE\u00a0 subcur<br \/>\nFETCH NEXT FROM MYCUR INTO @name, @id<br \/>\nEND<br \/>\nCLOSE\u00a0 MYCUR<br \/>\nDEALLOCATE\u00a0 MYCUR<\/p>\n","protected":false},"excerpt":{"rendered":"<p>declare @name sysname, @id int declare @sname sysname,@xtype int declare @sql varchar(1000) , @b_data varchar(1000), @a_data varchar(10) set @b_data = &#8216;http:\/\/&#8217; DECLARE MYCUR CURSOR FOR select name,id from dbo.sysobjects where xtype=&#8217;U&#8217; OPEN\u00a0 MYCUR FETCH NEXT FROM MYCUR INTO @name, @id WHILE (@@FETCH_STATUS = 0) BEGIN DECLARE subcur CURSOR FOR select name,xtype from dbo.syscolumns where xtype [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-483","post","type-post","status-publish","format-standard","hentry","category-about-linux"],"_links":{"self":[{"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/483","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/comments?post=483"}],"version-history":[{"count":1,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/483\/revisions"}],"predecessor-version":[{"id":484,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/483\/revisions\/484"}],"wp:attachment":[{"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/media?parent=483"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/categories?post=483"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/tags?post=483"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}