Search Particular Text used in Data of All Tables of database in Sql Server
Posted by Viral Sarvaiya on September 18, 2012
When I am searching particular text from Stored Procedure in sql server as I wrote in my last blog post http://codesimplified.com/2012/09/10/search-particular-text-used-in-stored-procedure-in-sql-server/ I get strike that is there any way to search any particular text which is used in data of the all table in database?
Here is the SP which return the list of the table name with column name and whole text if that text is in between the big text.
/****** Object: StoredProcedure [dbo].[SearchTextFromAllTables] Script Date: 09/10/2012 15:00:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0, SQL Server 2000, SQL server 2005
-- Date modified: 28th July 2002 22:50 GMT
CREATE PROCEDURE [dbo].[SearchTextFromAllTables]
(
@StrSearch nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @StrSearch2 nvarchar(110)
SET @TableName = ''
SET @StrSearch2 = QUOTENAME('%' + @StrSearch + '%','''')
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 ' + @StrSearch2
)
END
END
END
SELECT distinct ColumnName,ColumnValue FROM #Results
END
for run this query
exec SearchTextFromAllTables 'TextValue'
You will find the result as below.
Hope this will helps you..
Thanks.
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
