Search text values in All Tables and the SPs in same database

Search text values in All Tables and the SPs in same database This stored procedure/SQL Statement developed/tested in SQL Server 2008 and it has all backward compatability. · Search text value in All the Stored Procedure in same database Following statement will search text value in all the SP created in the same database. SELECT * FROM SYSCOMMENTS WHERE TEXT LIKE'%<search Value>%' · Search text values in All tables in same database This SP will search text string in all the database tables in all the columns. Ex: If you want to search one text like 'Country name: USA' then you run following SP and execute like Exec SearchInAllTables 'Country name: USA' You will get all the table name and related column name where this text exists. USE [Database name] GO CREATE PROCEDURE SearchInAllTables ( @searchString NVARCHAR(1000) ) AS BEGIN CREATE TABLE #OutputResults (ColumnNa NVARCHAR(500), ColumnVal NVARCHAR(4000)) SET NOCOUNT ON DECLARE @tableNa NVARCHAR(300), @columnNa NVARCHAR(200), @searchString2 NVARCHAR(1000) SET @tableNa = '' SET @searchString2 = QUOTENAME('%' + @searchString + '%','''') WHILE @tableNa IS NOT NULL BEGIN SET @columnNa = '' SET @tableNa = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @tableNa AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 ) WHILE (@tableNa IS NOT NULL) AND (@columnNa IS NOT NULL) BEGIN SET @columnNa = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@tableNa, 2) AND TABLE_NAME = PARSENAME(@tableNa, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') AND QUOTENAME(COLUMN_NAME) > @columnNa ) IF @columnNa IS NOT NULL BEGIN INSERT INTO #OutputResults EXEC ( 'SELECT ''' + @tableNa + '.' + @columnNa + ''', LEFT(' + @columnNa + ', 3630) FROM ' + @tableNa + ' (NOLOCK) ' + ' WHERE ' + @columnNa + ' LIKE ' + @searchString2 ) END END END SELECT ColumnNa, ColumnVal FROM #OutputResults END GO