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.

have you look here: http://stackoverflow.com/questions/591853/search-for-a-string-in-an-all-the-tables-rows-and-columns-of-a-db ? — Maksym Gontar, Feb 27 '09 at 10:25
I didn’t see that question. Unfortunately the solution there will not work on MS SQL 2000. The ROW_NUMBER() function was not introduced until SQL Server 2005. — NitroxDM, Feb 27 '09 at 17:01

score 6 · Accepted Answer · answered Feb 27 '09 at 07:00

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...

score 0 · Answer 2 · answered Jan 30 '13 at 15:10

I've found this script to be helpful... but as Marc noted, it wasn't really worth it. I've only used it a handful of times since I wrote it six months ago.

It only really comes in handy because there are a couple of tables in our dev environment which cause binding errors when you query them, and I always forget which ones.

BEGIN TRAN

declare @search nvarchar(100)
set @search = 'string to search for'

-- search whole database for text
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF nullif(object_id('tempdb..#tmpSearch'), 0) IS NOT NULL DROP TABLE #tmpSearch
CREATE TABLE #tmpSearch (
    ListIndex int identity(1,1),
    CustomSQL nvarchar(2000)
)
Print 'Getting tables...'
INSERT #tmpSearch (CustomSQL)
select 'IF EXISTS (select * FROM [' + TABLE_NAME + '] WHERE [' + COLUMN_NAME + '] LIKE ''%' + @search + '%'') BEGIN PRINT ''Table ' + TABLE_NAME + ', Column ' + COLUMN_NAME + ''';select * FROM [' + TABLE_NAME + '] WHERE [' + COLUMN_NAME + '] LIKE ''%' + @search + '%'' END'  FROM information_schema.columns
where DATA_TYPE IN ('ntext', 'nvarchar', 'uniqueidentifier', 'char', 'varchar', 'text')
and TABLE_NAME NOT IN ('table_you_dont_want_to_look_in', 'and_another_one') 


Print 'Searching...

'
declare @index int
declare @customsql nvarchar(2000)
WHILE EXISTS (SELECT * FROM #tmpSearch)
BEGIN
    SELECT @index = min(ListIndex) FROM #tmpSearch

    SELECT @customSQL = CustomSQL FROM #tmpSearch WHERE ListIndex = @index

    IF @customSql IS NOT NULL
        EXECUTE (@customSql)

    SET NOCOUNT ON
    DELETE #tmpSearch WHERE ListIndex = @index
    SET NOCOUNT OFF
END

print 'the end.'
ROLLBACK

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

2 Answers2

Related