Search any text or data in columns of SQL tables.

Search Text/Data in All Tables and Column in SQL Server

Following are the steps how you may search any text or data in columns of SQL tables.

1. Create the following stored procedure in the database you want to search any data in tables/columns

STORED PROCDURE

create PROC dbo.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

2. Run the procedure and give parameter execute SearchAllTables ‘Gr 4′

OUTPUT

Column Name	Column Value
[dbo].[BackupStudent].[Class_Enrolled]	Gr 4
[dbo].[BackupStudent].[Class_Enrolled]	Gr 4
[dbo].[BackupStudent].[Class_Enrolled]	Gr 4
[dbo].[BackupStudent].[Class_Enrolled]	Gr 4
[dbo].[BackupStudent].[Class_Enrolled]	Gr 4
[dbo].[BackupStudent].[Class_Enrolled]	Gr 4
[dbo].[BackupStudent].[Class_Enrolled]	Gr 4
[dbo].[BackupStudent].[Class_Enrolled]	Gr 4

Regards,

Sheryar Nizar

LinkedIn Profile :http://www.linkedin.com/in/sheryarnizar