在SQL Server中搜索所有表以查找字符串

Question

在SQL Server中搜索所有表以查找字符串

sqlsql-serversearch

289

是否有方法在SQL Server中搜索数据库中所有表格中的字符串？

我想要搜索字符串“john”。结果应该显示包含“john”的表和它们相应的行。

- Ramiz Raja

可能是在SQL Server中搜索特定值的所有表格，所有列的重复项。 - Mahmoud Gamal

2

可能是重复的问题：如何在 SQL Server 数据库中查找任何值？ - Jonathan Leffler

phpMyAdmin 允许非常简单地实现此功能。 - Matoeil

13个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- A. Morel · Answer 1

这个脚本允许在数据库中的所有表的所有字段的所有出现中进行搜索，无论数据类型如何！

实际上，如果它是一个具有数字数据类型的字段，则 TRY_CAST 函数会尝试将表达式转换为正确的数据类型。如果转换成功，则返回转换后的值。如果转换失败，则返回 NULL 而不是引发异常。

当您不确定数据类型或想要对任何搜索使用相同的脚本时，这非常方便。

    DECLARE @searchValue NVARCHAR(255) = '1856' -- Text or Number (text is converted in number if necessary)
    
    IF OBJECT_ID('tempdb..#SearchResults') IS NOT NULL
    BEGIN
        DROP TABLE #SearchResults
    END
    
    CREATE TABLE #SearchResults (
        TableName NVARCHAR(128),
        ColumnName NVARCHAR(128),
        Value NVARCHAR(MAX)
    )
    
    DECLARE @tableName NVARCHAR(128)
    DECLARE @columnName NVARCHAR(128)
    DECLARE @query NVARCHAR(MAX)
    DECLARE @type NVARCHAR(50)
    
    DECLARE db_cursor CURSOR FOR
    SELECT t.name AS TableName, c.name AS ColumnName, ty.name AS Type
    FROM sys.tables t
    JOIN sys.columns c ON t.object_id = c.object_id
    JOIN sys.types ty ON c.user_type_id = ty.user_type_id
    WHERE ty.name IN ('nvarchar', 'varchar', 'int', 'bigint', 'smallint', 'tinyint')
    
    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @tableName, @columnName, @type
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @type IN ('nvarchar', 'varchar')
        BEGIN
            SET @query = 'INSERT INTO #SearchResults (TableName, ColumnName, Value)
                          SELECT DISTINCT ''' + @tableName + ''', ''' + @columnName + ''', ' + 'CONVERT(NVARCHAR(MAX), ' + @columnName + ') FROM ' + @tableName + ' WHERE ' + @columnName + ' LIKE ''%' + @searchValue + '%'''
        END
        ELSE IF @type IN ('int', 'bigint', 'smallint', 'tinyint')
        BEGIN
            SET @query = 'INSERT INTO #SearchResults (TableName, ColumnName, Value)
                          SELECT DISTINCT ''' + @tableName + ''', ''' + @columnName + ''', ' + 'CONVERT(NVARCHAR(MAX), ' + @columnName + ') FROM ' + @tableName + ' WHERE ' + @columnName + ' = TRY_CAST(' + QUOTENAME(@searchValue, '''') + ' AS ' + @type + ')'
        END
    
        EXEC sp_executesql @query
    
        FETCH NEXT FROM db_cursor INTO @tableName, @columnName, @type
    END
    
    CLOSE db_cursor
    DEALLOCATE db_cursor
    
    SELECT * FROM #SearchResults
    
    DROP TABLE #SearchResults

- Abdul Khaliq · Answer 2

[CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS 
        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) > @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 + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) 
               END 
         END 
         END 
         
         SELECT ColumnName, ColumnValue FROM #Results 
         
         END
    
    EXEC SearchAllTables 'Computer'][1]


  [1]: https://koukia.ca/search-for-a-string-in-all-tables-and-all-fields-of-a-database-with-tsql-b6f8a1bfda37

- Dejan Dozet · Answer 3

这篇帖子中提到的答案已经被多次引用，我稍微做了一些修改，因为我也只需要在一个表中进行搜索：

（并且还使表名的输入更加简单）

ALTER PROC dbo.db_compare_SearchAllTables_sp
(
    @SearchStr nvarchar(100),
    @TableName nvarchar(256) = ''
)
AS
BEGIN
        if PARSENAME(@TableName, 2) is null
            set @TableName = 'dbo.' + QUOTENAME(@TableName, '"')

    declare @results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @ColumnName nvarchar(128) = '', @SearchStr2 nvarchar(110)
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
        IF @TableName <> ''
        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 + ' WITH (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
        ELSE
    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')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO @results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + ' WITH (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM @results
END