I’ve been working lately on writing extensions to a complex C# application which has gigantic Microsoft SQL database behind it. Unfortunately, the software is still alpha and only has limited documentation. This means I spend a lot of time guessing where data is stored. The problems lead me to write a very quick and dirty, brute force, stored procedure to search across all the tables and columns in a database.
Before I show the code, let me say that it is not really an example of a good practice. One could convincingly argue that it’s chock-full of bad practices. However, the only point behind the procedure is to find tables and columns which contain data.
Do not, I repeat, do not use this from a web page. It is only intended to be a quick tool you can use to search your database.
And with that disclaimer out of the way, here’s the procedure. I created the procedure in my master database, you might choose to put it somewhere else.
CREATE PROCEDURE search @DATABASE VARCHAR (400), @string VARCHAR (400) AS --create a table which will hold all of the table names and columns --FOR USER defined tables. CREATE TABLE #TableCache ( tableName VARCHAR (400), columnName VARCHAR (400) ) CREATE TABLE #Matches ( tableName VARCHAR (400), columnName VARCHAR (400) ) --create the statement to get all of the userdefined tables and columns --and insert them into the#TableCache DECLARE @sqlStatement AS VARCHAR (8000) --this does not search money, ntext, text, image types SET@sqlStatement = ' INSERT INTO #TableCache SELECT so.name as tableName, sc.name as columnName FROM ' + @DATABASE + '.dbo.sysobjects as so JOIN ' + @DATABASE + '.dbo.syscolumns as sc ON so.id = sc.id WHERE so.xtype = ' U ' AND sc.xtype NOT IN (34, 99, 35, 60)' EXEC (@sqlStatement) --declare vars FOR the upcoming CURSOR DECLARE @tableName VARCHAR (400) DECLARE @columnName VARCHAR (400) --create a cursor DECLARE tableColumn CURSOR FOR SELECT * FROM #TableCache --loop over the cursor OPEN tableColumn FETCH NEXT FROM tableColumn INTO @tableName, @columnName WHILE @@FETCH_STATUS = 0 BEGIN -- FOR every TABLE AND COLUMN IN the DATABASE CHECK TO see IF they contain -- the string being searched FOR. IF so, ADD them TO the #matches temp TABLE. SET @sqlStatement = ' IF ( SELECT COUNT(*) FROM ' + @DATABASE + '.dbo.' + @tableName + ' WHERE ' + @columnName + ' like ' % ' + @string + ' % ' ) > 0 INSERT INTO #Matches VALUES ('' + @tableName + '', '' + @columnName + '') ' EXEC (@sqlStatement) --next row, please FETCH NEXT FROM tableColumn INTO @tableName, @columnName END --destroy the cursor CLOSE tableColumn DEALLOCATE tableColumn --find matching tables SELECT * FROM #Matches --drop the temp table DROP TABLE #TableCache DROP TABLE #Matches
Assuming the procedure is in your Master database and that you have permissions to execute the procedure, you can call it from any database using this syntax.
</pre> master..search 'yourDatabase', 'text to search for'
When executed you will receive a record set containing two columns, “table” and “column”. Each record indicates that a particular column in a particular table contains a string.
The procedure ignores columns types of image, ntext, text, or money. I may have missed a few other types which may cause errors. I leave these up to you to fix.
If you know of a better way to do this please add a comment!