通过搜索 SQL Server 中的所有表来查找字符串 [英] Find a string by searching all tables in SQL Server

问题描述

有没有什么办法可以在SQL Server的一个数据库的所有表中搜索一个字符串?

Is there any way to search for a string in all tables of a database in SQL Server?

我想搜索字符串 say john.结果应显示包含 john 的表及其各自的行.

I want to search for string say john. The result should show the tables and their respective row that contain john.

推荐答案

如果你和我一样，在生产环境有一定的限制，你可能希望使用表变量代替临时表，以及临时查询而不是创建过程.

If you are like me and have certain restrictions in a production environment, you may wish to use a table variable instead of temp table, and an ad-hoc query rather than a create procedure.

当然取决于你的sql server实例，它必须支持表变量.

Of course depending on your sql server instance, it must support table variables.

我还添加了一个 USE 语句来缩小搜索范围

I also added a USE statement to narrow the search scope

USE DATABASE_NAME
DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'
DECLARE @Results TABLE (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

这篇关于通过搜索 SQL Server 中的所有表来查找字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！