SQL To search the entire MS SQL 2000 database for a value.

Question

I would like to search an entire MS SQL 2000 database for one value. This would be to aid development only. Keep that in mind when considering this question.

This will get all the table names and the column of the data type I'm looking for:

SELECT Columns.COLUMN_NAME, tables.TABLE_NAME
FROM INFORMATION_SCHEMA.Columns as Columns 
JOIN INFORMATION_SCHEMA.TABLES as tables 
On Columns.TABLE_NAME = tables.TABLE_NAME
WHERE Columns.DATA_TYPE = 'INT'

I was thinking something like this:

-- Vars
DECLARE @COUNTER INT
DECLARE @TOTAL INT
DECLARE @TABLE CHAR(128)
DECLARE @COLUMN CHAR(128)
DECLARE @COLUMNTYPE CHAR(128)
DECLARE @COLUMNVALUE INT

-- What we are looking for
SET @COLUMNTYPE = 'INT'
SET @COLUMNVALUE = 3
SET @COUNTER = 0

-- Find out how many possible columns exist
SELECT @TOTAL = COUNT(*)  
FROM INFORMATION_SCHEMA.Columns as Columns 
JOIN INFORMATION_SCHEMA.TABLES as tables 
On Columns.TABLE_NAME = tables.TABLE_NAME
WHERE Columns.DATA_TYPE = @COLUMNTYPE
PRINT CAST(@TOTAL AS CHAR) + 'possible columns'
WHILE @COUNTER < @TOTAL
BEGIN
    SET @COUNTER = @COUNTER +1
    -- ADD MAGIC HERE
END

Any ideas?

UPDATE I recently found this tool that works quite well.

Answer 1

Since it is dev only (and probably doesn't have to be very elegant), how about using TSQL to generate a pile of TSQL that you then copy back into the query window and execute?

SELECT 'SELECT * FROM [' + tables.TABLE_NAME + '] WHERE ['
       + Columns.Column_Name + '] = ' + CONVERT(varchar(50),@COLUMNVALUE)
FROM INFORMATION_SCHEMA.Columns as Columns
INNER JOIN INFORMATION_SCHEMA.TABLES as tables 
    On Columns.TABLE_NAME = tables.TABLE_NAME
WHERE Columns.DATA_TYPE = @COLUMNTYPE

It won't be pretty, but it should work... an alternative might be to insert something like the above into a table-variable, then loop over the table-variable using EXEC (@Sql). But for dev purposes it probably isn't worth it...