Search Particular Text used in Data of All Tables of database in Sql Server

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

Search Particular Text used in Stored Procedure in Sql Server

Few days ago I get very tedious job in database,
I have to search all stored procedure which have use one particular table.

I get following 1 option of query from sys table of sqlserver

SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%TBL_NAME%'    AND ROUTINE_TYPE='PROCEDURE'

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TBL_NAME%'

but this query only table table’s name, if We want particular string not table then this query will fail so below stored procedure will work.

CREATE PROCEDURE [dbo].[sp_search_code]
(
@SearchStr  varchar(100),
@RowsReturned int = NULL OUT
)
AS
BEGIN
SET NOCOUNT ON

SELECT DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name',
CASE
WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1
THEN 'Replication stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1
THEN 'Extended stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1
THEN 'Stored Procedure'
WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1
THEN 'Trigger'
WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1
THEN 'Table-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1
THEN 'Scalar-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
THEN 'Inline function'
END AS 'Object type',

'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) + '''' AS 'Run this command to see the object text'

FROM syscomments c
INNER JOIN
sysobjects o
ON c.id = o.id
WHERE c.text LIKE '%' + @SearchStr + '%' AND
encrypted = 0    AND
(
OBJECTPROPERTY(c.id, 'IsReplProc') = 1  OR
OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsProcedure') = 1  OR
OBJECTPROPERTY(c.id, 'IsTrigger') = 1  OR
OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
)

ORDER BY 'Object type', 'Object name'

SET @RowsReturned = @@ROWCOUNT
END

Run this query as like below

exec sp_search_code 'SearchText'

You will get require output of list of stored procedure name.

In this Stored Procedure i take one output parameter for check number of rows return by Stored Procedure.

Hope this will help you.

Thanks.