{"id":2679,"date":"2018-10-11T17:56:52","date_gmt":"2018-10-11T09:56:52","guid":{"rendered":"https:\/\/jason695.why3s.tw\/wordpress\/?p=2679"},"modified":"2019-12-02T10:00:43","modified_gmt":"2019-12-02T02:00:43","slug":"sql-table%e5%85%a7%e8%b3%87%e6%96%99%e7%9a%84%e5%85%a8%e6%96%87%e6%aa%a2%e7%b4%a2","status":"publish","type":"post","link":"https:\/\/jason695.why3s.tw\/wordpress\/2018\/10\/11\/sql-table%e5%85%a7%e8%b3%87%e6%96%99%e7%9a%84%e5%85%a8%e6%96%87%e6%aa%a2%e7%b4%a2\/","title":{"rendered":"SQL TABLE\u5167\u8cc7\u6599\u7684\u5168\u6587\u6aa2\u7d22"},"content":{"rendered":"<p><a href=\"https:\/\/blog.miniasp.com\/post\/2010\/07\/12\/Search-all-columns-of-all-tables-in-a-database-for-a-keyword.aspx\" target=\"_blank\" rel=\"noopener noreferrer\">https:\/\/blog.miniasp.com\/post\/2010\/07\/12\/Search-all-columns-of-all-tables-in-a-database-for-a-keyword.aspx<\/a><\/p>\n<p>\u611f\u8b1d\u4fdd\u54e5\uff0c\u8b9a\u5606\u4fdd\u54e5<\/p>\n\n\n<pre class=\"wp-block-preformatted\">DECLARE @SearchStr nvarchar(200) = N'\u4f7f\u7528\u8005\u672c\u6587'\n\n\n-- Copyright \u00a9 2002 Narayana Vyas Kondreddi. All rights reserved.\n-- Purpose: To search all columns of all tables for a given search string\n-- Written by: Narayana Vyas Kondreddi\n-- Site: http:\/\/vyaskn.tripod.com\n-- Tested on: SQL Server 7.0 and SQL Server 2000\n-- Date modified: 28th July 2002 22:50 GMT\n\n\nCREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))\n\nSET NOCOUNT ON\n\nDECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)\nSET  @TableName = ''\nSET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')\n\nWHILE @TableName IS NOT NULL\nBEGIN\n\tSET @ColumnName = ''\n\tSET @TableName = \n\t(\n\t\tSELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))\n\t\tFROM \tINFORMATION_SCHEMA.TABLES\n\t\tWHERE \t\tTABLE_TYPE = 'BASE TABLE'\n\t\t\tAND\tQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) &gt; @TableName\n\t\t\tAND\tOBJECTPROPERTY(\n\t\t\t\t\tOBJECT_ID(\n\t\t\t\t\t\tQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)\n\t\t\t\t\t\t ), 'IsMSShipped'\n\t\t\t\t\t       ) = 0\n\t)\n\n\tWHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)\n\tBEGIN\n\t\tSET @ColumnName =\n\t\t(\n\t\t\tSELECT MIN(QUOTENAME(COLUMN_NAME))\n\t\t\tFROM \tINFORMATION_SCHEMA.COLUMNS\n\t\t\tWHERE \t\tTABLE_SCHEMA\t= PARSENAME(@TableName, 2)\n\t\t\t\tAND\tTABLE_NAME\t= PARSENAME(@TableName, 1)\n\t\t\t\tAND\tDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')\n\t\t\t\tAND\tQUOTENAME(COLUMN_NAME) &gt; @ColumnName\n\t\t)\n\n\t\tIF @ColumnName IS NOT NULL\n\t\tBEGIN\n\t\t\tINSERT INTO #Results\n\t\t\tEXEC\n\t\t\t(\n\t\t\t\t'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) \n\t\t\t\tFROM ' + @TableName + ' (NOLOCK) ' +\n\t\t\t\t' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2\n\t\t\t)\n\t\tEND\n\tEND\t\nEND\n\nSELECT * FROM #Results\n\nDROP TABLE #Results<\/pre>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>https:\/\/blog.miniasp.com\/post\/2010\/07\/12\/Search-all-col &hellip; <a href=\"https:\/\/jason695.why3s.tw\/wordpress\/2018\/10\/11\/sql-table%e5%85%a7%e8%b3%87%e6%96%99%e7%9a%84%e5%85%a8%e6%96%87%e6%aa%a2%e7%b4%a2\/\" class=\"more-link\">\u95b1\u8b80\u5168\u6587 <span class=\"screen-reader-text\">SQL TABLE\u5167\u8cc7\u6599\u7684\u5168\u6587\u6aa2\u7d22<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[47],"tags":[],"class_list":["post-2679","post","type-post","status-publish","format-standard","hentry","category-sql-server","no-featured-image"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts\/2679","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/comments?post=2679"}],"version-history":[{"count":0,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts\/2679\/revisions"}],"wp:attachment":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/media?parent=2679"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/categories?post=2679"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/tags?post=2679"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}