Comprehensive Guide to Searching Specific Values Across All Tables and Columns in SQL Server Databases

Problem Background and Requirements Analysis

In database management and reverse engineering scenarios, there is often a need to locate specific values without prior knowledge of the table structure. The core challenge users face is how to efficiently identify all tables and columns containing specific character values (such as UIDs of char(64) type) in large SQL Server databases. Traditional methods like exporting the entire database for text search are highly inefficient and impractical when dealing with substantial data volumes.

Technical Implementation Principles

The solution is based on SQL Server's system catalog views, specifically INFORMATION_SCHEMA, which provides standardized access to database metadata. By querying INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS, column information for all user tables can be dynamically retrieved.

Key technical aspects include:

• Data Type Filtering: Searching only character-type columns (char, varchar, nchar, nvarchar) to avoid ineffective string matching on numeric or date columns
• Dynamic SQL Construction: Using string concatenation to generate LIKE query statements for each character column, enabling flexible column-level searching
• Cursor Alternative: Employing WHILE loops with MIN functions to simulate cursor behavior, avoiding the performance overhead of explicit cursors

Core Code Implementation

The following stored procedure implements comprehensive cross-table search functionality:

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

Implementation Details Analysis

Temporary Table Design: The #Results temporary table stores matching results, with the ColumnName field recording the complete identifier in "table.column" format, and ColumnValue limited to 3630 characters to avoid performance issues with overly long text.

Search String Processing: The QUOTENAME function safely escapes search patterns to prevent SQL injection attacks, while adding wildcard % characters enables fuzzy matching.

Table Traversal Logic: The outer loop iterates through all user tables (excluding system tables) in dictionary order, while the inner loop traverses character-type columns for each table, ensuring comprehensive coverage.

Performance Optimization: The NOLOCK hint reduces lock contention, suitable for read-only query scenarios. The LEFT function limits returned text length, balancing result completeness with query efficiency.

Cross-Database Comparison

Referencing similar requirements in MySQL environments, users often desire syntactic sugar like SELECT * FROM table WHERE * LIKE '%val%'. However, standard SQL does not support wildcard column searches, requiring metadata queries and dynamic SQL implementations across all database systems.

Key differences include:

• MySQL: Uses INFORMATION_SCHEMA or SHOW commands to retrieve metadata
• SQL Server: Provides more comprehensive system views and extended functions like OBJECTPROPERTY
• Implementation Complexity: SQL Server's approach requires explicit handling of schema names and object references, while MySQL supports simplified metadata access in certain versions

Application Scenarios and Limitations

Typical Applications:

• Database reverse engineering and documentation generation
• Data lineage analysis and impact assessment
• Sensitive data localization and compliance checking
• Test data validation and debugging support

Performance Considerations: Full table scans on large databases may consume significant resources. Recommendations include:

• Executing during business off-peak hours
• Considering batch processing by table partitions or time ranges
• Implementing pagination for result sets

Functional Extensions: The stored procedure can be enhanced based on specific requirements, such as:

• Adding exact match and regular expression support
• Implementing parallel processing to improve search speed
• Integrating into database management tools with graphical interfaces

Conclusion

Through systematic metadata queries and dynamic SQL techniques, the challenge of locating specific values in unknown database structures can be effectively addressed. The solution provided in this article is not only applicable to SQL Server environments but its design principles can also be adapted to other relational database systems. In practical applications, appropriate adjustments and optimizations should be made based on specific data scale, performance requirements, and security policies.