前言

对专案不熟的情况下，没有Table Schema就算对专案熟，也有Table Schema，但根本没有，也不想花时间去看Table schema光看到一大堆Table，就不想找了综合上述，总之就是懒得找

要怎么在茫茫资料库内寻找某个特定字串在哪边有被使用?

指引你的希望之光就在这！

SQL查询语法

搜寻有关Willian这个资料栏位

DECLARE @SearchStr nvarchar(100)SET @SearchStr = 'Willian'     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) > @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) > @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

查询结果

第一栏包含资料表与资料栏位名称，第二栏是资料内容，只要有包含Willian的资料都会被搜寻到，此结果可以看到会员资料表有个帐号就是Willian。

#参考连结
Find a value anywhere in a database