How to we get the table and the column name of known data value

dynamic-sqloracletable

I have a value (like 'IT department'), based on this I want to get the table and column name in which this value exists/present.

Best Answer

Just run this in a database.

As long as its not a text / ntext field..

SET NOCOUNT ON  /* AUTHOR DT 10/19/15 Server: Microsoft SQL Server */


CREATE TABLE #TBL_SEARCH (server_name varchar(100), db_name varchar(100), table_name varchar(40), raw_tablename varchar(max), [schema_nm] varchar(max), matching_field varchar(40), counts int,  
A_SAMPLE varchar(100) 
)

DECLARE Table_Column_Matches CURSOR
READ_ONLY
FOR (
    select '[' + OBJECT_SCHEMA_NAME(o.id) + '].[' + o.name + ']' as table_name, o.name as raw_table_name, 
    OBJECT_SCHEMA_NAME(o.id) as [schema_nm], c.name as field_name, t.name as datatype_field from sysobjects o
    join syscolumns c
    on o.id=c.id
    join systypes t
    on t.type=c.type and t.xtype=c.xtype and c.xusertype=t.xusertype
    where 
    o.type='U' and
    t.name IN ('char', 'nchar','nvarchar','varchar' )
)

DECLARE @table_name varchar(300)
DECLARE @field_name varchar(300)
DECLARE @raw_table_name varchar(300)
DECLARE @schema_nm varchar(300)
DECLARE @datatype_field varchar(300)
declare @query nvarchar(3000)

OPEN Table_Column_Matches

FETCH NEXT FROM Table_Column_Matches INTO @table_name, @raw_table_name, @schema_nm, @field_name, @datatype_field
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        set @query = 'SELECT @@SERVERNAME, DB_NAME(), ''' + @table_name + ''' as [table_name], ''[' + @raw_table_name + ']'' as raw_table_name,''[' + @schema_nm + ']'' as schema_nm, '''+ @field_name +''' as matching_field,
            COUNT([' + @field_name + ']) AS COUNTS, 
            MAX([' + @field_name + '] ) A_SAMPLE
            FROM  ' + @table_name + ' WITH (NOLOCK)
            WHERE UPPER([' + @field_name + ']) LIKE ''IT DEPT%''
        '

        INSERT INTO #TBL_SEARCH
        exec sp_executesql @query
    END
    FETCH NEXT FROM Table_Column_Matches INTO @table_name, @raw_table_name, @schema_nm, @field_name, @datatype_field
END

CLOSE Table_Column_Matches
DEALLOCATE Table_Column_Matches

select * from #TBL_SEARCH
where counts > 0
drop table #TBL_SEARCH
Related Question