Search all the tables in the Data Base for a Value

I just got a project i have no clue on how it was written and i need to get the table name where a certine value is stored.
there are no documentations for this project what so ever.
this is probably the most common scanario programmers get into when they need some sort of drastic measures.

i found this SP

CREATE PROCEDURE SearchDatabase

(@SearchString varchar(30),

@TableName varchar(30),

@ColumnName varchar(30))

AS

BEGIN

–Variables TO play within Stored PROCEDURE

DECLARE @strSQL VARCHAR(500)

DECLARE @sTableName VARCHAR(200)

DECLARE @sColumnName VARCHAR(200)

DECLARE @iRowCount INT

–Construct “Cursor Declare” statement based on Search Parameters

SET @strSQL = ‘DECLARE TabColCursor CURSOR FOR SELECT RTRIM(LTRIM(SU.NAME)) + ”.” + LTRIM(RTRIM(SO.NAME)), SC.NAME FROM SYSOBJECTS SO INNER JOIN SYSCOLUMNS SC ON SO.ID = SC.ID INNER JOIN SYSUSERS SU ON SO.UID = SU.UID WHERE SO.XTYPE = ”U”’

–Add the TABLE name Filter TO the “Cursor Declare” Statement

IF @TableName IS NOT NULL AND ltrim(rtrim(@TableName)) <> ”

BEGIN

SET @strSQL = @strSQL + ‘ AND SO.NAME LIKE ”’ + @TableName + ””

END

–Add the COLUMN name Filter TO the “Cursor Declare” Statement

IF @ColumnName IS NOT NULL AND ltrim(rtrim(@ColumnName)) <> ”

BEGIN

SET @strSQL = @strSQL + ‘ AND SC.NAME LIKE ”’ + @ColumnName + ””

END

–Execute the “Cursor Declare” statement. Creates “TabColCursor” Cursor

EXEC (@strSQL)

OPEN TabColCursor

FETCH NEXT FROM TabColCursor

INTO

@sTableName,

@sColumnName

–Check whether TabColCursor has ANY data

IF @@FETCH_STATUS = 0

BEGIN

–Create TEMPORARY TABLE TO store the result

IF EXISTS (SELECT * FROM tempdb..SYSOBJECTS WHERE NAME = ‘##tmpSearchResult’)

DELETE FROM ##tmpSearchResult

ELSE

CREATE TABLE ##tmpSearchResult (TableName varchar(300), ColumnName varchar(300), RowCount1 int)

WHILE @@FETCH_STATUS = 0

BEGIN

–Construct “Insert Statement” string

SET @strSQL = ‘INSERT INTO ##tmpSearchResult SELECT ”’ + @sTableName + ”’, ”’ + @sColumnName + ”’, (SELECT COUNT(‘ + @sColumnName + ‘) FROM ‘ + @sTableName + ‘ WHERE ‘ + @sColumnName + ‘ LIKE ”’ + @SearchString + ”’)’

–Execute “Insert Statement” string

EXEC (@strSQL)

IF @@ERROR <> 0

PRINT ‘Error Ignored. Search cannot be performed ON datatype LIKE text, ntext, AND image’

–Process Next Row

FETCH NEXT FROM TabColCursor

INTO

@sTableName,

@sColumnName

END

–Yes Yes Yes. Display the output.

–You can make USE OF this TEMPORARY TABLE IN other SPs/Functions/etc. based on the requirement

SELECT * FROM ##tmpSearchResult WHERE RowCount1 > 0

END

–Destroy CURSOR object

DEALLOCATE tabcolcursor

END

GO

GRANT EXECUTE ON dbo.sp_blockinglocks PUBLIC

GO

in the microsoft india forum.
it works slowly as suspected.
but very versetile.

Speak Your Mind

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.