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

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