Monday, June 15, 2015

TSQL - Search Column and its Value from ALL tables in database

Hi Guys,

Sometimes we try to find a specific Column Name from all the tables in the database. Below is the query-  
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name like '%Column_Name%'
ORDER BY schema_name, table_name

Of course everyone knows about it, you can google it.
But below is the query to find a  Column Name and its Value  from all the tables in database. Isn’t interesting !!

CREATE PROC SearchColumnAndItsValueFromAllTables 
( 
    @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' 
                               ) =
        ) 
 
        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 
END


Still this query can be optimized and extended further. But it helps to most of the developers. J

Cheers!!