Search a word in all table of a Database

Sometimes it happens that we forget where something was in the database. This Stored Procedure allows for searching every table and every field and returns a passed value. It's a powerful tool for searching but its not to be used in Programming.

 

Create PROC [dbo].[SearchWordsInAllTables]

(

@SearchStr nvarchar(100)

)

AS

BEGIN



CREATE
TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))


SET
NOCOUNT
ON


DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET @TableName =
''

SET @SearchStr2 =
QUOTENAME('%'
+ @SearchStr +
'%','''')


WHILE @TableName IS
NOT
NULL

BEGIN

SET @ColumnName =
''

SET @TableName =

(

SELECT
MIN(QUOTENAME(TABLE_SCHEMA)
+
'.'
+
QUOTENAME(TABLE_NAME))

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE =
'BASE TABLE'

AND QUOTENAME(TABLE_SCHEMA)
+
'.'
+
QUOTENAME(TABLE_NAME)
> @TableName

AND OBJECTPROPERTY(

OBJECT_ID(

QUOTENAME(TABLE_SCHEMA)
+
'.'
+
QUOTENAME(TABLE_NAME)

),
'IsMSShipped'

)
= 0

)


WHILE
(@TableName IS
NOT
NULL)
AND
(@ColumnName IS
NOT
NULL)

BEGIN

SET @ColumnName =

(

SELECT
MIN(QUOTENAME(COLUMN_NAME))

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA =
PARSENAME(@TableName, 2)

AND TABLE_NAME =
PARSENAME(@TableName, 1)

AND DATA_TYPE IN
('char',
'varchar',
'nchar',
'nvarchar')

AND QUOTENAME(COLUMN_NAME)
> @ColumnName

)


IF @ColumnName IS
NOT
NULL

BEGIN

INSERT
INTO #Results

EXEC

(

'SELECT '''
+ @TableName +
'.'
+ @ColumnName +
''', LEFT('
+ @ColumnName +
', 3630)

FROM '
+ @TableName +
' (NOLOCK) '
+

' WHERE '
+ @ColumnName +
' LIKE '
+ @SearchStr2

)

END

END

END


SELECT ColumnName, ColumnValue FROM #Results

END

 Exec [SearchWordsInAllTables] 'gmail'