Find table(s) name and column(s) name by providing a value in SQLServer-2000 Database

Many times(specially bug fixing period) developer wants to search a specific value is present in database or the value is in which tables & which columns. If value found in database then easily understand the value is comming from database otherwise think it comes from application code or xml file.

Most of the case data value comes from database. So developer first guess the value is in database. But it should be proved. otherwise bug cannot be solved.


I worte 2 stored procedure for searching data in database. Any one can call it form application and make a tools or directly execute it from database. the code examples are


1st SP
=====

create proc dbo.GetColumnName
(
@tableName varchar(1000),
@value varchar(1000)
)
as

SET NOCOUNT ON

declare @colName varchar(1000),
@sql nvarchar(4000),
@counter int;

set @sql = '';

set @counter = 1;

create table #Temp (sl int identity(1,1), [Name] varchar(1000));

create table #TempNew ([Value] varchar(1000));

create table #Result(name varchar(1000));

set @sql = N'insert #temp ([Name]) select name from syscolumns
where id = object_id(''' + @tableName + ''')' + ' AND XType NOT IN(36,61,58,34,35,165,99) ORDER BY name ASC';


EXEC SP_EXECUTESQL @sql while(1=1) begin

select @colName = [Name] from #temp where sl = @counter;

if (@@rowcount = 0) break;

--print @colName;

set @sql = N'insert #TempNew([Value])
select ' + @colName + ' from ' + @tableName
+ ' where ' + 'CAST('+ @colName + ' AS VARCHAR(1000))=''' + @value + '''';

EXEC sp_executesql @sql;

IF (@@ROWCOUNT > 0) --print @colName;

INSERT INTO #Result(name) VALUES(@colName);

set @counter = @counter + 1;

end
--print @counter; select all * from #Result;
go
--exec dbo.GetColumnName 'dbo.reader' , 3


2nd SP
=====

CREATE PROCEDURE dbo.GetColumnNameFromDB
(
@Value VARCHAR(8000)
)
AS

SET NOCOUNT ON
DECLARE @NewTable VARCHAR(1000)

IF (OBJECT_ID('tempdb.dbo.##Result') IS NOT NULL)
DROP TABLE tempdb.dbo.##Result;

CREATE TABLE tempdb.dbo.##Result(SL INT IDENTITY(1,1), TableName Varchar(1000), ColumnName varchar(1000))

--DECLARE @Result TABLE(SL INT IDENTITY(1,1), TableName Varchar(1000), ColumnName varchar(1000)) DECLARE C1 CURSOR FOR SELECT table_name FROM information_schema.tables
WHERE table_type='BASE TABLE'

OPEN C1

FETCH NEXT FROM C1 INTO @NewTable;

WHILE(@@FETCH_STATUS = 0) BEGIN

INSERT ##Result(ColumnName)
EXEC dbo.GetColumnName @TableName = @newTable, @Value = @Value;

IF (@@ROWCOUNT > 0)

UPDATE ##Result SET TableName = @NewTable WHERE SL = (SELECT MAX(SL) FROM ##Result)

FETCH NEXT FROM C1 INTO @NewTable; END CLOSE C1 DEALLOCATE C1;

SELECT ALL * FROM ##Result;

GO