Find a string by searching all tables in SQL Server

Question

Is there any way to search for a string in all tables of a database in SQL Server?

I want to search for string say john. The result should show the tables and their respective row that contain john.

Answer 1

If you are like me and have certain restrictions in a production environment, you may wish to use a table variable instead of temp table, and an ad-hoc query rather than a create procedure.

Of course depending on your sql server instance, it must support table variables.

I also added a USE statement to narrow the search scope

USE DATABASE_NAME DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT' DECLARE @Results TABLE (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', 'int', 'decimal')                 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 

Answer 2

A bit late but hopefully useful.

Why not try some of the third party tools that can be integrated into SSMS.

I’ve worked with ApexSQL Search (100% free) with good success for both schema and data search and there is also SSMS tools pack that has this feature (not free for SQL 2012 but quite affordable).

Stored procedure above is really great; it’s just that this is way more convenient in my opinion. Also, it would require some slight modifications if you want to search for datetime columns or GUID columns and such…