DougHughes.net

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!