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!
Comments on: "Brute Force SQL Search Procedure" (3)
how large is the DB? (ie: # of tables & gb’s of data)
There’s not that much data as the application is still alpha. The problem is knowing where data is stored when you don’t have direct access to documentation or the application authors. This helped me, for instance, find all the tables which made refernce to a UUID, or contained a phrase like "Event List Screen" or some such thing.
Without the database in front of I’m I’d say 100 + tables.