danielwertheim

danielwertheim


notes from a passionate developer

Developer that lives by the mantra "code is meant to be shared".

Share


Tags


Disclaimer

This is a personal blog. The opinions expressed here represent my own and not those of my employer, nor current or previous. All content is published "as is", without warranty of any kind and I don't take any responsibility and can't be liable for any claims, damages or other liabilities that might be caused by the content.

Scary post, T-SQL finding table, schema and column where column value is X

Daniel WertheimDaniel Wertheim

This is a scary T-SQL post with some ugly code and unfriendly variables as the result of a quick hack to answer a StackOverflow question:

In T-SQL, if just know value of one column say: ‘Ferrari’, then is it possible to know the column name table name, database name using any query or trick?

I will create a stored procedure for this and I will pass it a custom table type with possible matches of table and columns. So lets first create the table type:

create type Meta as table (  
    rid int identity primary key, 
    ts varchar(100), 
    tn varchar(100), 
    cn varchar(100))

Easy, now the ugly looking procedure:

create proc [dbo].[foo]  
(   
    @t Meta readonly,
    @v varchar(50)
)
AS  
begin  
    declare @max int
    declare @c bit
    declare @i int
    declare @ts varchar(100)
    declare @tn varchar(100)
    declare @cn varchar(100)
    declare @q nvarchar(max)

    set @max = (select max(rid) from @t);
    set @c = 0;
    set @i = 0;
    while(@i  0)
        begin
            set @c = 1;
            break;
        end
    end

    if(@c = 1)
        select @ts + '.' + @tn + '.' + @cn;
    else
        select null;
end  

Finally, put it to use:

-- Extract possible matches
declare @t Meta  
insert into @t  
select  
    c.TABLE_SCHEMA, 
    c.TABLE_NAME, 
    c.COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS c  
where  
    c.DATA_TYPE like '%varchar'
    and c.CHARACTER_MAXIMUM_LENGTH >= len(@v)

--Search
declare @v varchar(50)  
set @v = 'Ferrari'  
exec dbo.foo @t = @t, @v = @v  

Every match will be output in the result-grid: dbo.MyTable.MyColumn.

Again. Not pretty and I hope there are plenty of room for tweaking, since it was just a hack. Have fun.

//Daniel

Developer that lives by the mantra "code is meant to be shared".

Comments