blocks|key|1397326|text|您可以查询sys.tables数据库视图以获得表的名称，然后使用此查询构建另一个查询，在此查询的基础上执行更新。例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1397327|select+'select+*+from+'%2Bname+from+sys.tables|code-block|syntax|javascript|1397328|将为您提供一个脚本，该脚本将对系统目录中的所有表运行select+*，您可以更改select子句中的字符串来进行更新，只要您知道要更新的所有表上的列名都相同，那么您的脚本将如下所示：|1397329|select+'update+'%2Bname%2B'+set+comments+=+''(*)''%2Bcomments+where+comments+like+''%25comment+to+be+updated%25''+'+from+sys.tables|1397330|然后，您还可以在tables查询上进行谓词，使其仅包含具有特定格式名称的表，或者位于您想要为其创建更新脚本的子集中的表。|1397331|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You could query the sys.tables database view to get out the names of the tables, and then use this query to build yourself another query to do the update on the back of that. For instance:

<pre><code>select 'select * from '+name from sys.tables
</code></pre>

will give you a script that will run a select * against all the tables in the system catalog, you could alter the string in the select clause to do your update, as long as you know the column name is the same on all the tables you wish to update, so your script would look something like:

<pre><code>select 'update '+name+' set comments = ''(*)''+comments where comments like ''%comment to be updated%'' ' from sys.tables
</code></pre>

You could also then predicate on the tables query to only include tables that have a name in a certain format, or are in a subset you want to create the update script for.

blocks|key|1397372|text|我刚刚更新了我的博客帖子，以更正您的脚本中的错误，Jeff，您可以在这里看到更新的脚本：Search+all+fields+in+SQL+Server+Database|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1397373|根据要求，这是脚本，以防您需要它，但我建议您查看博客帖子，因为我会不时更新它|1397374|DECLARE+@SearchStr+nvarchar(100)
SET+@SearchStr+=+'##+YOUR+STRING+HERE+##'
 
 
--+Copyright+©+2002+Narayana+Vyas+Kondreddi.+All+rights+reserved.
--+Purpose:+To+search+all+columns+of+all+tables+for+a+given+search+string
--+Written+by:+Narayana+Vyas+Kondreddi
--+Site:+http://vyaskn.tripod.com
--+Updated+and+tested+by+Tim+Gaunt
--+http://www.thesitedoctor.co.uk
--+http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search%2BEvery%2BTable%2BAnd%2BField%2BIn%2BA%2BSQL%2BServer%2BDatabase%2BUpdated.aspx
--+Tested+on:+SQL+Server+7.0,+SQL+Server+2000,+SQL+Server+2005+and+SQL+Server+2010
--+Date+modified:+03rd+March+2011+19:00+GMT
CREATE+TABLE+#Results+(ColumnName+nvarchar(370),+ColumnValue+nvarchar(3630))
 
SET+NOCOUNT+ON
 
DECLARE+@TableName+nvarchar(256),+@ColumnName+nvarchar(128),+@SearchStr2+nvarchar(110)
SET +@TableName+=+''
SET+@SearchStr2+=+QUOTENAME('%25'+%2B+@SearchStr+%2B+'%25','''')
 
WHILE+@TableName+IS+NOT+NULL
 
BEGIN
++++SET+@ColumnName+=+''
++++SET+@TableName+=+
++++(
++++++++SELECT+MIN(QUOTENAME(TABLE_SCHEMA)+%2B+'.'+%2B+QUOTENAME(TABLE_NAME))
++++++++FROM+++++INFORMATION_SCHEMA.TABLES
++++++++WHERE+++++++++TABLE_TYPE+=+'BASE+TABLE'
++++++++++++AND   +QUOTENAME(TABLE_SCHEMA)+%2B+'.'+%2B+QUOTENAME(TABLE_NAME)+>+@TableName
++++++++++++AND   +OBJECTPROPERTY(
++++++++++++++++++++OBJECT_ID(
++++++++++++++++++++++++QUOTENAME(TABLE_SCHEMA)+%2B+'.'+%2B+QUOTENAME(TABLE_NAME)
++++++++++++++++++++++++ ),+'IsMSShipped'
++++++++++++++++++++++++   )+=+0
++++)
 
++++WHILE+(@TableName+IS+NOT+NULL)+AND+(@ColumnName+IS+NOT+NULL)
++++++++ 
++++BEGIN
++++++++SET+@ColumnName+=
++++++++(
++++++++++++SELECT+MIN(QUOTENAME(COLUMN_NAME))
++++++++++++FROM+++++INFORMATION_SCHEMA.COLUMNS
++++++++++++WHERE+++++++++TABLE_SCHEMA   +=+PARSENAME(@TableName,+2)
++++++++++++++++AND   +TABLE_NAME   +=+PARSENAME(@TableName,+1)
++++++++++++++++AND   +DATA_TYPE+IN+('char',+'varchar',+'nchar',+'nvarchar',+'int',+'decimal')
++++++++++++++++AND   +QUOTENAME(COLUMN_NAME)+>+@ColumnName
++++++++)
 
++++++++IF+@ColumnName+IS+NOT+NULL
++++++++ 
++++++++BEGIN
++++++++++++INSERT+INTO+#Results
++++++++++++EXEC
++++++++++++(
++++++++++++++++'SELECT+'''+%2B+@TableName+%2B+'.'+%2B+@ColumnName+%2B+''',+LEFT('+%2B+@ColumnName+%2B+',+3630)+FROM+'+%2B+@TableName+%2B+'+(NOLOCK)+'+%2B
++++++++++++++++'+WHERE+'+%2B+@ColumnName+%2B+'+LIKE+'+%2B+@SearchStr2
++++++++++++)
++++++++END
++++END   
END
 
SELECT+ColumnName,+ColumnValue+FROM+#Results
 
DROP+TABLE+#Results|code-block|syntax|javascript|1397375|entityMap|0|LINK|mutability|MUTABLE|url|http://thesitedoctor.co.uk/blog/search-every-table-and-field-in-a-sql-server-database-updated^0|18|14|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

I've just updated my blog post to correct the error in the script that you were having Jeff, you can see the updated script here: <a href="http://thesitedoctor.co.uk/blog/search-every-table-and-field-in-a-sql-server-database-updated" rel="noreferrer">Search all fields in SQL Server Database</a>

As requested, here's the script in case you want it but I'd recommend reviewing the blog post as I do update it from time to time

<pre><code>DECLARE @SearchStr nvarchar(100)
SET @SearchStr = '## YOUR STRING HERE ##'
 
 
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Updated and tested by Tim Gaunt
-- http://www.thesitedoctor.co.uk
-- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
-- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
-- Date modified: 03rd March 2011 19:00 GMT
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
 
SET NOCOUNT ON
 
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 
WHILE @TableName IS NOT NULL
 
BEGIN
 SET @ColumnName = ''
 SET @TableName = 
 (
 SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_TYPE = 'BASE TABLE'
 AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) &gt; @TableName
 AND    OBJECTPROPERTY(
 OBJECT_ID(
 QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
  ), 'IsMSShipped'
    ) = 0
 )
 
 WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  
 BEGIN
 SET @ColumnName =
 (
 SELECT MIN(QUOTENAME(COLUMN_NAME))
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA    = PARSENAME(@TableName, 2)
 AND    TABLE_NAME    = PARSENAME(@TableName, 1)
 AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
 AND    QUOTENAME(COLUMN_NAME) &gt; @ColumnName
 )
 
 IF @ColumnName IS NOT NULL
  
 BEGIN
 INSERT INTO #Results
 EXEC
 (
 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
 ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
 )
 END
 END   
END
 
SELECT ColumnName, ColumnValue FROM #Results
 
DROP TABLE #Results
</code></pre>

blocks|key|1181913|text|下面的查询可以工作，但非常慢...从vyaskn.tripod.com复制|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1181914|Declare+@SearchStr+nvarchar(100)

SET++@SearchStr='Search+String'+BEGIN

CREATE+TABLE+#Results+(ColumnName+nvarchar(370),+ColumnValue+nvarchar(3630))

SET+NOCOUNT+ON

DECLARE+@TableName+nvarchar(256),+@ColumnName+nvarchar(128),
+@SearchStr2+nvarchar(110)++SET++@TableName+=+''++++SET+@SearchStr2+=
+QUOTENAME('%25'+%2B+@SearchStr+%2B+'%25','''')

WHILE+@TableName+IS+NOT+NULL++++
BEGIN+++++++
++SET+@ColumnName+=+''++++++
++SET+@TableName+=++(
++++SELECT+MIN(QUOTENAME(TABLE_SCHEMA)+%2B+'.'+%2B
++++QUOTENAME(TABLE_NAME))+FROM+INFORMATION_SCHEMA.TABLES+
++++WHERE
++++TABLE_TYPE+=+'BASE+TABLE'
++++AND+QUOTENAME(TABLE_SCHEMA)+%2B+'.'+%2B+QUOTENAME(TABLE_NAME)+>+@TableName
++++AND+OBJECTPROPERTY(
++++++OBJECT_ID(QUOTENAME(TABLE_SCHEMA)+%2B+'.'+%2B+QUOTENAME(TABLE_NAME)),
++++++++'IsMSShipped')+=+0)

++WHILE+(@TableName+IS+NOT+NULL)+AND+(@ColumnName+IS+NOT+NULL)++++++
++BEGIN
++++SET+@ColumnName+=+(
++++++SELECT+MIN(QUOTENAME(COLUMN_NAME))
++++++FROM+INFORMATION_SCHEMA.COLUMNS
++++++WHERE+TABLE_SCHEMA+=+PARSENAME(@TableName,+2)
++++++++AND+TABLE_NAME+=+PARSENAME(@TableName,+1)
++++++AND+DATA_TYPE+IN+('char',+'varchar',+'nchar',+'nvarchar')
++++++AND+QUOTENAME(COLUMN_NAME)+>+@ColumnName)
++++++IF+@ColumnName+IS+NOT+NULL++++++++++++
++++++BEGIN
++++++INSERT+INTO+#Results
++++++EXEC
++++++(
++++++++'SELECT+'''+%2B+@TableName+%2B+'.'+%2B+@ColumnName+%2B+''',+LEFT('+%2B+@ColumnName+%2B+
++++++++++',+3630)+FROM+'+%2B+@TableName+%2B+'+(NOLOCK)+'+%2B
++++++++'+WHERE+'+%2B+@ColumnName+%2B+'+LIKE+'+%2B+@SearchStr2
++++++)+++++++++++++
++++++END+++++++
++++END+++++
++END

++SELECT+ColumnName,+ColumnValue+FROM+#Results+END|code-block|syntax|javascript|1181915|entityMap|0|LINK|mutability|MUTABLE|url|http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm^0|I|H|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

The below Query works but very slow... copied from <a href="http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm" rel="nofollow noreferrer">vyaskn.tripod.com</a>

<pre><code>Declare @SearchStr nvarchar(100)

SET @SearchStr='Search String' BEGIN

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128),
 @SearchStr2 nvarchar(110) SET @TableName = '' SET @SearchStr2 =
 QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL 
BEGIN 
 SET @ColumnName = '' 
 SET @TableName = (
 SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' +
 QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES 
 WHERE
 TABLE_TYPE = 'BASE TABLE'
 AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) &gt; @TableName
 AND OBJECTPROPERTY(
 OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),
 'IsMSShipped') = 0)

 WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) 
 BEGIN
 SET @ColumnName = (
 SELECT MIN(QUOTENAME(COLUMN_NAME))
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
 AND TABLE_NAME = PARSENAME(@TableName, 1)
 AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
 AND QUOTENAME(COLUMN_NAME) &gt; @ColumnName)
 IF @ColumnName IS NOT NULL 
 BEGIN
 INSERT INTO #Results
 EXEC
 (
 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + 
 ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
 ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
 ) 
 END 
 END 
 END

 SELECT ColumnName, ColumnValue FROM #Results END
</code></pre>

blocks|key|1397423|text|我在这里发布了一个：FullParam+SQL+Blog|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1397424|/*+Reto+Egeter,+fullparam.wordpress.com+*/

DECLARE+@SearchStrTableName+nvarchar(255),+@SearchStrColumnName+nvarchar(255),+@SearchStrColumnValue+nvarchar(255),+@SearchStrInXML+bit,+@FullRowResult+bit,+@FullRowResultRows+int
SET+@SearchStrColumnValue+=+'%25searchthis%25'+/*+use+LIKE+syntax+*/
SET+@FullRowResult+=+1
SET+@FullRowResultRows+=+3
SET+@SearchStrTableName+=+NULL+/*+NULL+for+all+tables,+uses+LIKE+syntax+*/
SET+@SearchStrColumnName+=+NULL+/*+NULL+for+all+columns,+uses+LIKE+syntax+*/
SET+@SearchStrInXML+=+0+/*+Searching+XML+data+may+be+slow+*/

IF+OBJECT_ID('tempdb..#Results')+IS+NOT+NULL+DROP+TABLE+#Results
CREATE+TABLE+#Results+(TableName+nvarchar(128),+ColumnName+nvarchar(128),+ColumnValue+nvarchar(max),ColumnType+nvarchar(20))

SET+NOCOUNT+ON

DECLARE+@TableName+nvarchar(256)+=+'',@ColumnName+nvarchar(128),@ColumnType+nvarchar(20),+@QuotedSearchStrColumnValue+nvarchar(110),+@QuotedSearchStrColumnName+nvarchar(110)
SET+@QuotedSearchStrColumnValue+=+QUOTENAME(@SearchStrColumnValue,'''')
DECLARE+@ColumnNameTable+TABLE+(COLUMN_NAME+nvarchar(128),DATA_TYPE+nvarchar(20))

WHILE+@TableName+IS+NOT+NULL
BEGIN
++++SET+@TableName+=+
++++(
++++++++SELECT+MIN(QUOTENAME(TABLE_SCHEMA)+%2B+'.'+%2B+QUOTENAME(TABLE_NAME))
++++++++FROM++++INFORMATION_SCHEMA.TABLES
++++++++WHERE+++++++TABLE_TYPE+=+'BASE+TABLE'
++++++++++++AND+TABLE_NAME+LIKE+COALESCE(@SearchStrTableName,TABLE_NAME)
++++++++++++AND+QUOTENAME(TABLE_SCHEMA)+%2B+'.'+%2B+QUOTENAME(TABLE_NAME)+>+@TableName
++++++++++++AND+OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA)+%2B+'.'+%2B+QUOTENAME(TABLE_NAME)),+'IsMSShipped')+=+0
++++)
++++IF+@TableName+IS+NOT+NULL
++++BEGIN
++++++++DECLARE+@sql+VARCHAR(MAX)
++++++++SET+@sql+=+'SELECT+QUOTENAME(COLUMN_NAME),DATA_TYPE
++++++++++++++++FROM++++INFORMATION_SCHEMA.COLUMNS
++++++++++++++++WHERE+++++++TABLE_SCHEMA++++=+PARSENAME('''+%2B+@TableName+%2B+''',+2)
++++++++++++++++AND+TABLE_NAME++=+PARSENAME('''+%2B+@TableName+%2B+''',+1)
++++++++++++++++AND+DATA_TYPE+IN+('+%2B+CASE+WHEN+ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%25',''),'_',''),'[',''),']',''),'-',''))+=+1+THEN+'''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'','+ELSE+''+END+%2B+'''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier'''+%2B+CASE+@SearchStrInXML+WHEN+1+THEN+',''xml'''+ELSE+''+END+%2B+')
++++++++++++++++AND+COLUMN_NAME+LIKE+COALESCE('+%2B+CASE+WHEN+@SearchStrColumnName+IS+NULL+THEN+'NULL'+ELSE+''''+%2B+@SearchStrColumnName+%2B+''''+END++%2B+',COLUMN_NAME)'
++++++++INSERT+INTO+@ColumnNameTable
++++++++EXEC+(@sql)
++++++++WHILE+EXISTS+(SELECT+TOP+1+COLUMN_NAME+FROM+@ColumnNameTable)
++++++++BEGIN
++++++++++++PRINT+@ColumnName
++++++++++++SELECT+TOP+1+@ColumnName+=+COLUMN_NAME,@ColumnType+=+DATA_TYPE+FROM+@ColumnNameTable
++++++++++++SET+@sql+=+'SELECT+'''+%2B+@TableName+%2B+''','''+%2B+@ColumnName+%2B+''','+%2B+CASE+@ColumnType+WHEN+'xml'+THEN+'LEFT(CAST('+%2B+@ColumnName+%2B+'+AS+nvarchar(MAX)),+4096),'''+
++++++++++++WHEN+'timestamp'+THEN+'master.dbo.fn_varbintohexstr('%2B+@ColumnName+%2B+'),'''
++++++++++++ELSE+'LEFT('+%2B+@ColumnName+%2B+',+4096),'''+END+%2B+@ColumnType+%2B+'''+
++++++++++++++++++++FROM+'+%2B+@TableName+%2B+'+(NOLOCK)+'+%2B
++++++++++++++++++++'+WHERE+'+%2B+CASE+@ColumnType+WHEN+'xml'+THEN+'CAST('+%2B+@ColumnName+%2B+'+AS+nvarchar(MAX))'+
++++++++++++++++++++WHEN+'timestamp'+THEN+'master.dbo.fn_varbintohexstr('%2B+@ColumnName+%2B+')'
++++++++++++++++++++ELSE+@ColumnName+END+%2B+'+LIKE+'+%2B+@QuotedSearchStrColumnValue
++++++++++++INSERT+INTO+#Results
++++++++++++EXEC(@sql)
++++++++++++IF+@@ROWCOUNT+>+0+IF+@FullRowResult+=+1+
++++++++++++BEGIN
++++++++++++++++SET+@sql+=+'SELECT+TOP+'+%2B+CAST(@FullRowResultRows+AS+VARCHAR(3))+%2B+'+'''+%2B+@TableName+%2B+'''+AS+[TableFound],'''+%2B+@ColumnName+%2B+'''+AS+[ColumnFound],''FullRow>''+AS+[FullRow>],*'+%2B
++++++++++++++++++++'+FROM+'+%2B+@TableName+%2B+'+(NOLOCK)+'+%2B
++++++++++++++++++++'+WHERE+'+%2B+CASE+@ColumnType+WHEN+'xml'+THEN+'CAST('+%2B+@ColumnName+%2B+'+AS+nvarchar(MAX))'+
++++++++++++++++++++WHEN+'timestamp'+THEN+'master.dbo.fn_varbintohexstr('%2B+@ColumnName+%2B+')'
++++++++++++++++++++ELSE+@ColumnName+END+%2B+'+LIKE+'+%2B+@QuotedSearchStrColumnValue
++++++++++++++++EXEC(@sql)
++++++++++++END
++++++++++++DELETE+FROM+@ColumnNameTable+WHERE+COLUMN_NAME+=+@ColumnName
++++++++END+
++++END
END
SET+NOCOUNT+OFF

SELECT+TableName,+ColumnName,+ColumnValue,+ColumnType,+COUNT(*)+AS+Count+FROM+#Results
GROUP+BY+TableName,+ColumnName,+ColumnValue,+ColumnType|code-block|syntax|javascript|1397425|entityMap|0|LINK|mutability|MUTABLE|url|http://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns/^0|A|I|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

I published one here: <a href="http://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns/">FullParam SQL Blog</a>

<pre><code>/* Reto Egeter, fullparam.wordpress.com */

DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
SET @SearchStrColumnValue = '%searchthis%' /* use LIKE syntax */
SET @FullRowResult = 1
SET @FullRowResultRows = 3
SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */
SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */
SET @SearchStrInXML = 0 /* Searching XML data may be slow */

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')
DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))

WHILE @TableName IS NOT NULL
BEGIN
 SET @TableName = 
 (
 SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_TYPE = 'BASE TABLE'
 AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
 AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) &gt; @TableName
 AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
 )
 IF @TableName IS NOT NULL
 BEGIN
 DECLARE @sql VARCHAR(MAX)
 SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = PARSENAME(''' + @TableName + ''', 2)
 AND TABLE_NAME = PARSENAME(''' + @TableName + ''', 1)
 AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')
 AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END + ',COLUMN_NAME)'
 INSERT INTO @ColumnNameTable
 EXEC (@sql)
 WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
 BEGIN
 PRINT @ColumnName
 SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
 SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),''' 
 WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),'''
 ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + ''' 
 FROM ' + @TableName + ' (NOLOCK) ' +
 ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' 
 WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
 ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
 INSERT INTO #Results
 EXEC(@sql)
 IF @@ROWCOUNT &gt; 0 IF @FullRowResult = 1 
 BEGIN
 SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow&gt;'' AS [FullRow&gt;],*' +
 ' FROM ' + @TableName + ' (NOLOCK) ' +
 ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' 
 WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
 ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
 EXEC(@sql)
 END
 DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
 END 
 END
END
SET NOCOUNT OFF

SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
GROUP BY TableName, ColumnName, ColumnValue, ColumnType
</code></pre>

I have a specific value, let's say string 'comments'. I need to find all instances of this in the database as I need to do an update on the format to change it to (*) Comments.

How can I do this? The database is in SQL Server 2000 format.

Search all tables, all columns for a specific value SQL Server

SQL Server

数据库

我有一个特定值，比方说字符串'comments‘。我需要在数据库中找到它的所有实例，因为我需要对格式进行更新，以将其更改为(*)注释。我该怎么做呢？数据库采用SQL Server 2000格式。

问搜索特定值的所有表、所有列SQL Server
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问搜索特定值的所有表、所有列SQL ServerEN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问搜索特定值的所有表、所有列SQL Server
EN