{"id":2547,"date":"2013-03-15T03:16:51","date_gmt":"2013-03-15T03:16:51","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2547"},"modified":"2013-05-24T07:52:04","modified_gmt":"2013-05-24T07:52:04","slug":"%e5%9c%a8%e6%89%80%e6%9c%89%e8%a1%a8%e4%b8%ad%e6%9f%a5%e8%af%a2%e6%9f%90%e4%b8%aa%e5%80%bc","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2013\/03\/15\/%e5%9c%a8%e6%89%80%e6%9c%89%e8%a1%a8%e4%b8%ad%e6%9f%a5%e8%af%a2%e6%9f%90%e4%b8%aa%e5%80%bc\/","title":{"rendered":"\u5168\u5e93\u67e5\u627e:\u5728\u6240\u6709\u8868\u4e2d\u67e5\u8be2\u67d0\u4e2a\u503c"},"content":{"rendered":"<p><span style=\"font-size: 12px;\">&nbsp;<\/span><\/p>\n<pre class=\"brush:sql;first-line:1;pad-line-numbers:true;highlight:null;collapse:false;\">\r\nDeclare @keytosearch varchar(max), @Database_Selected varchar(50)\r\n\r\nset @keytosearch =&#39;%london%&#39; \r\nset @Database_Selected= &#39;Northwind&#39;\r\n\r\nDeclare @Table varchar(100), @Table_Name Cursor, @Count_Column int, @Result nvarchar(4000),@ID int,@ID_inserted int,@Count_Table int, @data_type varchar(10)\r\n\r\nset @ID_inserted=0   \r\nset @Count_Table=0\r\n\r\nDECLARE @column varchar(max), @Column_Name CURSOR\r\n--Variable Delaration end\r\n\r\n--Second Cursor start\r\ndeclare @informationName varchar(50), @SysName varchar(50), @Var varchar(5)\r\n\r\nset @informationName=@Database_Selected+&#39;.&#39;+&#39;information_schema.COLUMNS&#39;\r\nSet @SysName=@Database_Selected+&#39;.&#39;+&#39;sys.objects&#39;\r\nSet @Var=&#39;u&#39;\r\n\r\n--Database Selected start\r\nCreate Table #SearchResult(ID int identity(1,1) not null, TableName varchar(50), ColumnName varchar(50))\r\nCreate Table #SearchTestResult(ID int identity(1,1) not null, ColumnName varchar(max))\r\nCreate Table #Table_Name(ID int identity(1,1) not null, table_name varchar(50))\r\n\r\nIF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=&#39;BASE TABLE&#39; AND TABLE_NAME=&#39;Column_Nam&#39;) \r\n  drop table Column_Nam\r\n\r\ninsert into #Table_Name exec (&#39;Select name from &#39;+@SysName +&#39; where type= &#39;&#39;&#39;+@Var+&#39;&#39;&#39;&#39;)\r\n\r\n--First Cursor open\r\nSET @Table_Name = CURSOR FOR Select table_name from #Table_Name \r\n\r\nopen @Table_Name \r\n\r\nFetch Next from @Table_Name into @Table\r\n\r\nWHILE @@FETCH_STATUS = 0\r\n BEGIN \r\n  set @Count_Table =@Count_Table+1\r\n  --Second cursor opened\r\n  --print &#39;Select column_name from  &#39;+@informationName +&#39; where table_name= &#39;&#39;&#39;+@Table+&#39;&#39;&#39;&#39;s\r\n  Exec(&#39;SELECT column_name,data_type INTO Column_Nam FROM  &#39;+ @informationName +&#39; where table_name = &#39;&#39;&#39;+@Table+&#39;&#39;&#39;&#39;)\r\n  SET @Column_Name = CURSOR FOR (select column_name from Column_Nam )\r\n\r\n  OPEN @Column_Name FETCH NEXT FROM @Column_Name INTO @column\r\n \r\n  set @Table=@Database_Selected+&#39;.[dbo].[&#39;+@Table+&#39;]&#39;\r\n  WHILE @@FETCH_STATUS = 0\r\n   BEGIN\r\n    set @data_type= (SELECT top 1 data_type FROM Column_Nam Where column_name= @column)\r\n\r\n    if @data_type is not null and (@data_type=&#39;varchar&#39; or @data_type=&#39;nvarchar&#39; or @data_type=&#39;Text&#39;)\r\n     begin \r\n      set @Result=null\r\n      if @column like &#39;%-%&#39; begin  set @Result =(&#39;SELECT &#39;&#39;&#39; + @column +&#39;&#39;&#39; FROM &#39; + @Table  \r\n                +&#39; Where &#39;&#39;&#39; + @column + &#39;&#39;&#39; Like &#39;&#39;&#39;+@keytosearch+&#39;&#39;&#39;&#39;) end\r\n        else\r\n\t\t set @Result =(&#39;SELECT &#39; + @column +&#39; FROM &#39; + @Table  +&#39; Where &#39; + @column + &#39; Like &#39;&#39;&#39;+@keytosearch+&#39;&#39;&#39;&#39;)\r\n\r\n      insert into #SearchTestResult exec(@Result)\r\n\r\n      set @ID=0\r\n      set @ID=(Select ID from #SearchTestResult where ID=Scope_identity())\r\n\r\n\t  if @ID is not null \r\n\t   begin\r\n        set @ID_inserted=(select top 1 ID from #SearchResult order by ID desc)\r\n            if @ID_inserted = @ID \r\n             begin \r\n              print &#39;&#39; \r\n             end \r\n           else  \r\n\t        insert into #SearchResult values (@Table,@column)\r\n       end  \r\n    end\r\n    FETCH NEXT FROM @Column_Name INTO @column \r\n   END  \r\n  CLOSE @Column_Name  \r\n  DEALLOCATE @Column_Name\r\n  --Second cursor closed\r\n\r\n  drop table Column_Nam\r\n\r\n  Fetch Next from @Table_Name into @Table \r\n End \r\n\r\nclose @Table_Name  \r\n\r\nDeallocate @Table_Name\r\n--First Cursor Closed\r\n\r\nSelect * from #SearchResult\r\n<\/pre>\n<p>\n\u53c2\u8003\uff1a<a href=\"http:\/\/www.sqlservercentral.com\/articles\/T-SQL\/96882\/\">Search for a Value Throughout Your Database<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; Declare @keytosearch varchar(max), @Database_Sel [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2547","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2547","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2547"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2547\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2547"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2547"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2547"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}