Searching a string in all tables in a Database

The following  Stored Procedure can help us to search for a string within all columns and rows of all tables in a Database.


CREATE PROC SearchAllTables
(
@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

Now after creating the Stored procedure, let us execute it.


EXEC SearchAllTables '245'
GO
This entry was posted in Knowledge Sharing, SQL Server, Troubleshooting. Bookmark the permalink.

4 Responses to Searching a string in all tables in a Database

  1. Anonymous says:

    Took me time to read all the comments, but I really enjoyed the article. It proved to be very helpful to me and I am sure to all the commenterâ??s here! Itâ??s always nice when you can not only be informed, but also entertained! Iâ??m sure you had fun writing this article.

  2. Highly insightful thank you, I do believe your followers could perhaps want far more content such as this continue the very good content.

  3. I REALLY liked your post and blog! It took me a minute bit to find your siteâ?¦but I bookmarked it. Would you mind if I posted a link back to your post?

  4. Thanks for this post. I don’t know a large number of links to forums but my friends seem to be able to spend a lot of time on them that’s for sure.

Leave a Reply