Find a string by searching all tables in SQL Server Management Studio 2008

Question

Is there any way to search for a string in all tables of a database in SQL Server Management Studio 2008   I want to search for string say john  The result should show the tables and their respective row that contain john

User · Accepted Answer

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

User · Answer

Improving the amazing answer from  Brandon  I added type to ntext and xml using castings  BEGIN TRAN  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   gt   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    ntext    xml                   AND    QUOTENAME COLUMN NAME   gt   ColumnName                    IF  ColumnName IS NOT NULL          BEGIN             INSERT INTO  Results             EXEC                                SELECT        TableName          ColumnName        LEFT  cast      ColumnName     as nvarchar max     3630                   FROM      TableName      NOLOCK                        WHERE  cast      ColumnName     as nvarchar max    LIKE      SearchStr2                       END     END     END  SELECT ColumnName  ColumnValue FROM  Results  ROLLBACK

User · Answer

There s no need for nested looping  outer looping through tables and inner looping through all table columns   One can retrieve all  or arbitrary selected filtered  table-column combinations from INFORMATION SCHEMA COLUMNS and in one loop simply pass through  search  all of them   DECLARE  search VARCHAR 100    table SYSNAME   column SYSNAME  DECLARE curTabCol CURSOR FOR     SELECT c TABLE SCHEMA         c TABLE NAME  c COLUMN NAME     FROM INFORMATION SCHEMA COLUMNS c     JOIN INFORMATION SCHEMA TABLES t        ON t TABLE NAME c TABLE NAME AND t TABLE TYPE  BASE TABLE  -- avoid views     WHERE c DATA TYPE IN   varchar   nvarchar   -- searching only in these column types     --AND c COLUMN NAME IN   NAME   DESCRIPTION   -- searching only in these column names  SET  search  john   OPEN curTabCol FETCH NEXT FROM curTabCol INTO  table   column  WHILE    FETCH STATUS   0  BEGIN     EXECUTE  IF EXISTS                SELECT   FROM      table     WHERE      column              search                      PRINT        table          column             FETCH NEXT FROM curTabCol INTO  table   column END  CLOSE curTabCol DEALLOCATE curTabCol

User · Answer

The answer that was mentioned in this post already several times I have adopted a little bit because I needed to search in only one table too    and also made input for the table name a bit more simpler   ALTER PROC dbo db compare SearchAllTables sp        SearchStr nvarchar 100        TableName nvarchar 256         AS BEGIN         if PARSENAME  TableName  2  is null             set  TableName    dbo     QUOTENAME  TableName            declare  results TABLE ColumnName nvarchar 370   ColumnValue nvarchar 3630        SET NOCOUNT ON      DECLARE  ColumnName nvarchar 128         SearchStr2 nvarchar 110      SET  SearchStr2   QUOTENAME        SearchStr                     IF  TableName  lt  gt             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   gt   ColumnName                            IF  ColumnName IS NOT NULL             BEGIN                 INSERT INTO  results                 EXEC                                        SELECT        TableName          ColumnName        LEFT      ColumnName      3630                       FROM      TableName     WITH  NOLOCK                            WHERE      ColumnName     LIKE      SearchStr2                               END         END             ELSE     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   gt   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   gt   ColumnName                            IF  ColumnName IS NOT NULL             BEGIN                 INSERT INTO  results                 EXEC                                        SELECT        TableName          ColumnName        LEFT      ColumnName      3630                       FROM      TableName     WITH  NOLOCK                            WHERE      ColumnName     LIKE      SearchStr2                               END         END         END      SELECT ColumnName  ColumnValue FROM  results END

User · Answer

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

User · Answer

This was very helpful   I wanted to import this function to a Postgre SQL database   Thought i would share it with anyone who is interested   Will have them a few hours   Note  this function creates a list of SQL statements that can be copied and executed on the Postgre database   Maybe someone smarter then me can get Postgre to create and execute the statements all in one function   CREATE OR REPLACE FUNCTION SearchAllTables  search text  RETURNS TABLE  txt text   as  funct      DECLARE   COUNT int        SQL text  BEGIN     EXECUTE  SELECT COUNT 0  FROM INFORMATION SCHEMA COLUMNS                     WHERE    DATA TYPE     text                        AND          table schema     public     INTO   COUNT       RETURN QUERY          SELECT CASE WHEN ROW NUMBER   OVER  ORDER BY table name   lt    COUNT THEN               SELECT        table name         column name        AS tbl         column name       AS col FROM  public         table name        WHERE       column name        ILIKE          search          UNION ALL               ELSE               SELECT        table name         column name        AS tbl         column name       AS col FROM  public         table name        WHERE       column name        ILIKE          search                   END AS txt                      FROM     INFORMATION SCHEMA COLUMNS                     WHERE    DATA TYPE    text                       AND          table schema    public   END  funct  LANGUAGE plpgsql

User · Answer

To update TechDo s answer for SQL server 2012  You need to change   FROM      TableName      NOLOCK    to FROM      TableName    WITH  NOLOCK        Other wise you will get the following error  Deprecated feature  Table hint without WITH  is not supported in this version of SQL Server   Below is the complete updated stored procedure   CREATE PROC 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   gt   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   gt   ColumnName                            IF  ColumnName IS NOT NULL              BEGIN                 INSERT INTO  Results                 EXEC                                        SELECT        TableName          ColumnName        LEFT      ColumnName      3630                       FROM      TableName    WITH  NOLOCK                            WHERE      ColumnName     LIKE      SearchStr2                               END         END         END      SELECT ColumnName  ColumnValue FROM  Results END

User · Answer

I have written a SP for the this which returns the search results in form of Table name  the Column names in which the search keyword string was found as well as the searches the corresponding rows as shown in below screen shot     This might not be the most efficient solution but you can always modify and use it according to your need   IF OBJECT ID  sp KeywordSearch    P   IS NOT NULL     DROP PROC sp KeywordSearch GO  CREATE PROCEDURE sp KeywordSearch  KeyWord NVARCHAR 100  AS BEGIN     DECLARE  Result TABLE          TableName NVARCHAR 300            ColumnName NVARCHAR MAX        DECLARE  Sql NVARCHAR MAX            TableName NVARCHAR 300            ColumnName NVARCHAR 300            Count INT      DECLARE  tableCursor CURSOR      SET  tableCursor   CURSOR LOCAL SCROLL FOR     SELECT  N SELECT  Count   COUNT 1  FROM  dbo       T TABLE NAME      WITH  NOLOCK  WHERE CAST      C COLUMN NAME                  AS NVARCHAR MAX   LIKE         KeyWord   N                   T TABLE NAME              C COLUMN NAME     FROM    INFORMATION SCHEMA TABLES AS T WITH  NOLOCK      INNER JOIN INFORMATION SCHEMA COLUMNS AS C WITH  NOLOCK      ON      T TABLE SCHEMA   C TABLE SCHEMA AND             T TABLE NAME   C TABLE NAME     WHERE   T TABLE TYPE    BASE TABLE  AND             C TABLE SCHEMA    dbo  AND             C DATA TYPE NOT IN   image    timestamp        OPEN  tableCursor     FETCH NEXT FROM  tableCursor INTO  Sql   TableName   ColumnName      WHILE    FETCH STATUS   0      BEGIN         SET  Count   0          EXEC sys sp executesql              Sql              N  Count INT OUTPUT                Count OUTPUT          IF  Count  gt  0         BEGIN             INSERT  INTO  Result                      TableName  ColumnName              VALUES    TableName   ColumnName          END          FETCH NEXT FROM  tableCursor INTO  Sql   TableName   ColumnName     END      CLOSE  tableCursor     DEALLOCATE  tableCursor      SET  tableCursor   CURSOR LOCAL SCROLL FOR     SELECT  SUBSTRING TB Sql  1  LEN TB Sql  - 3  AS Sql  TB TableName  SUBSTRING TB Columns  1  LEN TB Columns  - 1  AS Columns     FROM     SELECT R TableName   SELECT R2 ColumnName        FROM  Result AS R2 WHERE R TableName   R2 TableName FOR XML PATH      AS Columns                       SELECT   FROM     R TableName     WITH  NOLOCK  WHERE                          SELECT  CAST     R2 ColumnName     AS NVARCHAR MAX   LIKE         KeyWord        OR                        FROM    Result AS R2                      WHERE  R TableName   R2 TableName                     FOR                      XML PATH      AS Sql              FROM    Result AS R              GROUP BY R TableName  TB     ORDER BY TB Sql      OPEN  tableCursor     FETCH NEXT FROM  tableCursor INTO  Sql   TableName   ColumnName      WHILE    FETCH STATUS   0      BEGIN         PRINT  Sql         SELECT   TableName AS  Table                    ColumnName AS Columns         EXEC  Sql           FETCH NEXT FROM  tableCursor INTO  Sql   TableName   ColumnName     END      CLOSE  tableCursor     DEALLOCATE  tableCursor  END

User · Answer

A bit late  but you can easily find a string with this query  DECLARE  search string  VARCHAR 100    table name     SYSNAME   table id       INT   column name    SYSNAME   sql string     VARCHAR 2000   SET  search string    StringtoSearch   DECLARE tables cur CURSOR FOR SELECT ss name       so name  name   object id FROM sys objects so INNER JOIN sys schemas ss ON so schema id   ss schema id WHERE  type    U   OPEN tables cur  FETCH NEXT FROM tables cur INTO  table name   table id  WHILE    FETCH STATUS   0  BEGIN     DECLARE columns cur CURSOR FOR SELECT name FROM sys columns WHERE object id    table id          AND system type id IN  167  175  231  239       OPEN columns cur      FETCH NEXT FROM columns cur INTO  column name         WHILE    FETCH STATUS   0          BEGIN             SET  sql string    IF EXISTS  SELECT   FROM      table name     WHERE       column name                   LIKE         search string         PRINT        table name           column name                     EXECUTE  sql string           FETCH NEXT FROM columns cur INTO  column name         END      CLOSE columns cur  DEALLOCATE columns cur  FETCH NEXT FROM tables cur INTO  table name   table id END  CLOSE tables cur DEALLOCATE tables cur

[sql] Find a string by searching all tables in SQL Server Management Studio 2008

Examples related to sql

Examples related to sql-server

Examples related to search