这是来自上面链接的Stored Proc-我所做的唯一更改是用temp表替换了一个表变量，因此您不必记住每次都将其删除。
CREATE PROC SearchAllTables( @SearchStr nvarchar(100))ASBEGIN-- 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-- Tested on: SQL Server 7.0 and SQL Server 2000-- Date modified: 28th July 2002 22:50 GMTDECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))SET NOCOUNT onDECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)SET @TableName = ''SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')WHILE @TableName IS NOT NULLBEGIN 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) > @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) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO @Results EXEC ( 'SELECt ''' + @TableName + '.' + @ColumnName + ''', LEFt(' + @ColumnName + ', 3630) FROM ' + @TableName + ' WHERe ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END ENDSELECt ColumnName, ColumnValue FROM @ResultsEND
