Find a value anywhere in a database

Question

Given a    how do I discover in what table and column it could be found within   I don t care if it s fast  it just needs to work

User · Answer

For Development purpose you can just export the required tables data into a single HTML and make a direct search on it

User · Answer

Based on bnkdev s answer I modified Narayana s Code to search all columns even numeric ones   It ll run slower  but this version actually finds all matches not just those found in text columns   I can t thank this guy enough  Saved me days of searching by hand   CREATE PROC SearchAllTables     SearchStr nvarchar 100    AS BEGIN  -- 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 and SQL Server 2000 -- Date modified  28th July 2002 22 50 GMT   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 QUOTENAME COLUMN NAME   gt   ColumnName                    IF  ColumnName IS NOT NULL         BEGIN             INSERT INTO  Results             EXEC                                SELECT        TableName          ColumnName        LEFT CONVERT varchar max        ColumnName       3630                   FROM      TableName      NOLOCK                        WHERE CONVERT varchar max        ColumnName      LIKE      SearchStr2                       END     END  END  SELECT ColumnName  ColumnValue FROM  Results END

User · Answer

It s my way to resolve this question  Tested on SQLServer2008R2  CREATE PROC SearchAllTables  SearchStr nvarchar 100  AS BEGIN DECLARE  dml nvarchar max    N           IF OBJECT ID  tempdb dbo  Results   IS NOT NULL DROP TABLE dbo  Results CREATE TABLE dbo  Results    tablename  nvarchar 100       ColumnName  nvarchar 100       Value  nvarchar max     SELECT  dml      SELECT       s name         t name       AS  tablename                          c name       AS  ColumnName   CAST     QUOTENAME c name                      AS nvarchar max   AS  Value  FROM     QUOTENAME s name          QUOTENAME t name                      NOLOCK  WHERE CAST     QUOTENAME c name      AS nvarchar max   LIKE              SearchStr         FROM sys schemas s JOIN sys tables t ON s schema id   t schema id                    JOIN sys columns c ON t object id   c object id                    JOIN sys types ty ON c system type id   ty system type id AND c  user type id   ty  user type id WHERE t is ms shipped   0 AND ty name NOT IN   timestamp    image    sql variant    INSERT dbo  Results EXEC sp executesql  dml  SELECT   FROM dbo  Results END

User · Answer

Here  very sweet and small solution   1  create a store procedure   create procedure get table  find str varchar 50  as  begin   declare  col name varchar 500    tab name varchar 500     declare  find tab TABLE table name varchar 100   column name varchar 100       DECLARE tab col cursor for    select C name as  col name   T name as tab name   from sys tables as T   left outer join sys columns as C on  C object id T object id   left outer join sys types as TP on  C system type id TP system type id   where type  U     and TP name in  text   ntext   varchar   char   nvarchar   nchar       open tab col   fetch next from tab col into  col name   tab name    while   FETCH STATUS   0   begin             insert into  find tab      exec  select         tab name              col name       from      tab name          where      col name            find str       group by           col name     having count    gt 0         fetch next from tab col into  col name   tab name    end   CLOSE tab col      DEALLOCATE tab col     select table name  column name from  find tab   end                               2  call procedure by calling store procedure  exec get table  serach string

User · Answer

For Development purpose you can just export the required tables data into a single HTML and make a direct search on it

User · Answer

I was looking for a just a numeric value   6 84 - using the other answers here I was able to limit my search to this Declare  sourceTable Table id INT NOT NULL IDENTITY PRIMARY KEY  table name varchar 1000   column name varchar 1000   Declare  resultsTable Table id INT NOT NULL IDENTITY PRIMARY KEY  table name varchar 1000    Insert into  sourceTable table name  column name  select schema name t schema id          t name as table   c name as column name from sys columns c join sys tables t on t object id   c object id where type name user type id  in   decimal    numeric    smallmoney    money    float    real   order by table   c column id   DECLARE db cursor CURSOR FOR Select table name  column name from  sourceTable DECLARE  mytablename VARCHAR 1000   DECLARE  mycolumnname VARCHAR 1000    OPEN db cursor  FETCH NEXT FROM db cursor INTO  mytablename   mycolumnname  WHILE    FETCH STATUS   0 BEGIN     Insert into  ResultsTable table name      EXEC  SELECT        mytablename          mycolumnname        FROM      mytablename      NOLOCK            WHERE      mycolumnname     6 84       FETCH NEXT FROM db cursor INTO  mytablename   mycolumnname   END  CLOSE db cursor  DEALLOCATE db cursor  Select Distinct table name  from  ResultsTable

User · Answer

If you need to run such search only once then you can probably go with any of the scripts already shown in other answers  But otherwise  I   d recommend using ApexSQL Search for this  It   s a free SSMS addin and it really saved me a lot of time   Before running any of the scripts you should customize it based on the data type you want to search  If you know you are searching for datetime column then there is no need to search through nvarchar columns  This will speed up all of the queries above

User · Answer

I wrote once a tool for myself to do exactly that thing   a7 SqlTools  It s free and open-source   github link

User · Answer

This might help you  - from Narayana Vyas  It searches all columns of all tables in a given database  I have used it before and it works   This is the Stored Proc from the above link - the only change I made was substituting the temp table for a table variable so you don t have to remember to drop it each time   CREATE PROC SearchAllTables        SearchStr nvarchar 100    AS BEGIN  -- 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 and SQL Server 2000 -- Date modified  28th July 2002 22 50 GMT  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                   AND QUOTENAME COLUMN NAME   gt   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

User · Answer

Based on bnkdev s answer I modified Narayana s Code to search all columns even numeric ones   It ll run slower  but this version actually finds all matches not just those found in text columns   I can t thank this guy enough  Saved me days of searching by hand   CREATE PROC SearchAllTables     SearchStr nvarchar 100    AS BEGIN  -- 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 and SQL Server 2000 -- Date modified  28th July 2002 22 50 GMT   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 QUOTENAME COLUMN NAME   gt   ColumnName                    IF  ColumnName IS NOT NULL         BEGIN             INSERT INTO  Results             EXEC                                SELECT        TableName          ColumnName        LEFT CONVERT varchar max        ColumnName       3630                   FROM      TableName      NOLOCK                        WHERE CONVERT varchar max        ColumnName      LIKE      SearchStr2                       END     END  END  SELECT ColumnName  ColumnValue FROM  Results END

User · Answer

If you have phpMyAdmin installed use its Search feature   Select your DataBase   Be sure you do have selected DataBase  not a table  otherwise you ll get a completely different search dialog    Click Search tab  List item Choose the search term you want   Choose the tables to search

User · Answer

This might help you  - from Narayana Vyas  It searches all columns of all tables in a given database  I have used it before and it works   This is the Stored Proc from the above link - the only change I made was substituting the temp table for a table variable so you don t have to remember to drop it each time   CREATE PROC SearchAllTables        SearchStr nvarchar 100    AS BEGIN  -- 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 and SQL Server 2000 -- Date modified  28th July 2002 22 50 GMT  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                   AND QUOTENAME COLUMN NAME   gt   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

User · Answer

This might help you  - from Narayana Vyas  It searches all columns of all tables in a given database  I have used it before and it works   This is the Stored Proc from the above link - the only change I made was substituting the temp table for a table variable so you don t have to remember to drop it each time   CREATE PROC SearchAllTables        SearchStr nvarchar 100    AS BEGIN  -- 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 and SQL Server 2000 -- Date modified  28th July 2002 22 50 GMT  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                   AND QUOTENAME COLUMN NAME   gt   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

User · Answer

It s my way to resolve this question  Tested on SQLServer2008R2  CREATE PROC SearchAllTables  SearchStr nvarchar 100  AS BEGIN DECLARE  dml nvarchar max    N           IF OBJECT ID  tempdb dbo  Results   IS NOT NULL DROP TABLE dbo  Results CREATE TABLE dbo  Results    tablename  nvarchar 100       ColumnName  nvarchar 100       Value  nvarchar max     SELECT  dml      SELECT       s name         t name       AS  tablename                          c name       AS  ColumnName   CAST     QUOTENAME c name                      AS nvarchar max   AS  Value  FROM     QUOTENAME s name          QUOTENAME t name                      NOLOCK  WHERE CAST     QUOTENAME c name      AS nvarchar max   LIKE              SearchStr         FROM sys schemas s JOIN sys tables t ON s schema id   t schema id                    JOIN sys columns c ON t object id   c object id                    JOIN sys types ty ON c system type id   ty system type id AND c  user type id   ty  user type id WHERE t is ms shipped   0 AND ty name NOT IN   timestamp    image    sql variant    INSERT dbo  Results EXEC sp executesql  dml  SELECT   FROM dbo  Results END

User · Answer

Another way using JOIN and CURSOR   USE My Database   -- Store results in a local temp table so that   I m using a -- local temp table so that I can access it in SP EXECUTESQL  create table  tmp       tbl nvarchar max       col nvarchar max       val nvarchar max         declare  tbl nvarchar max   declare  col nvarchar max   declare  q nvarchar max   declare  search nvarchar max     my search key    -- Create a cursor on all columns in the database declare c cursor for SELECT tbls TABLE NAME  cols COLUMN NAME  FROM INFORMATION SCHEMA TABLES AS tbls JOIN INFORMATION SCHEMA COLUMNS AS cols ON tbls TABLE NAME   cols TABLE NAME  -- For each table and column pair  see if the search value exists  open c fetch next from c into  tbl   col while   FETCH STATUS   0 begin     -- Look for the search key in current table column and if found add it to the results      SET  q    INSERT INTO  tmp SELECT        tbl               col             col     FROM      tbl     WHERE      col     LIKE         search             EXEC SP EXECUTESQL  q     fetch next from c into  tbl   col end close c deallocate c  -- Get results select   from  tmp  -- Remove local temp table  drop table  tmp

User · Answer

Based on bnkdev s answer I modified Narayana s Code to search all columns even numeric ones   It ll run slower  but this version actually finds all matches not just those found in text columns   I can t thank this guy enough  Saved me days of searching by hand   CREATE PROC SearchAllTables     SearchStr nvarchar 100    AS BEGIN  -- 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 and SQL Server 2000 -- Date modified  28th July 2002 22 50 GMT   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 QUOTENAME COLUMN NAME   gt   ColumnName                    IF  ColumnName IS NOT NULL         BEGIN             INSERT INTO  Results             EXEC                                SELECT        TableName          ColumnName        LEFT CONVERT varchar max        ColumnName       3630                   FROM      TableName      NOLOCK                        WHERE CONVERT varchar max        ColumnName      LIKE      SearchStr2                       END     END  END  SELECT ColumnName  ColumnValue FROM  Results END

User · Answer

This is my independent take on this question that I use for my own work   It works in SQL2000 and greater  allows wildcards  column filtering  and will search most of the normal data types   A pseudo-code description could be select   from   where any like  foo   -------------------------------------------------------------------------------- -- Search all columns in all tables in a database for a string  -- Does not search  image  sql variant or user-defined types  -- Exact search always for money and smallmoney  no wildcards for matching these  -------------------------------------------------------------------------------- declare  SearchTerm nvarchar 4000  -- Can be max for SQL2005  declare  ColumnName sysname  -------------------------------------------------------------------------------- -- SET THESE  -------------------------------------------------------------------------------- set  SearchTerm   N foo  -- Term to be searched for  wildcards okay set  ColumnName   N   -- Use to restrict the search to certain columns  wildcards okay  null or empty string for all cols -------------------------------------------------------------------------------- -- END SET --------------------------------------------------------------------------------  set nocount on  declare  TabCols table         id int not null primary key identity       table schema sysname not null       table name sysname not null       column name sysname not null       data type sysname not null   insert into  TabCols  table schema  table name  column name  data type      select t TABLE SCHEMA  c TABLE NAME  c COLUMN NAME  c DATA TYPE     from INFORMATION SCHEMA TABLES t         join INFORMATION SCHEMA COLUMNS c on t TABLE SCHEMA   c TABLE SCHEMA             and t TABLE NAME   c TABLE NAME     where 1   1         and t TABLE TYPE    base table          and c DATA TYPE not in   image    sql variant           and c COLUMN NAME like case when len  ColumnName   gt  0 then  ColumnName else     end     order by c TABLE NAME  c ORDINAL POSITION  declare        table schema sysname        table name sysname        column name sysname        data type sysname        exists nvarchar 4000  -- Can be max for SQL2005         sql nvarchar 4000  -- Can be max for SQL2005         where nvarchar 4000  -- Can be max for SQL2005         run nvarchar 4000  -- Can be max for SQL2005   while exists  select null from  TabCols  begin      select top 1            table schema   table schema            table name   table name            exists    select null from      table schema           table name      where 1   0             sql    select             table schema           table name             as TABLE NAME    from      table schema           table name      where 1   0             where          from  TabCols     order by id      while exists  select null from  TabCols where table schema    table schema and table name    table name  begin          select top 1                column name   column name                data type   data type         from  TabCols         where table schema    table schema             and table name    table name         order by id          -- Special case for money         if  data type in   money    smallmoney   begin             if isnumeric  SearchTerm    1 begin                 set  where    where     or       column name        cast        SearchTerm       as      data type       -- could also cast the column as varchar for wildcards             end         end         -- Special case for xml         else if  data type    xml  begin             set  where    where     or cast       column name      as nvarchar max   like        SearchTerm                end         -- Special case for date         else if  data type in   date    datetime    datetime2    datetimeoffset    smalldatetime    time   begin             set  where    where     or convert nvarchar 50         column name       121  like        SearchTerm                end         -- Search all other types         else begin             set  where    where     or       column name      like        SearchTerm                end          delete from  TabCols where table schema    table schema and table name    table name and column name    column name      end      set  run    if exists      exists    where      begin      sql    where     print        table name       end      print  run     exec sp executesql  run  end  set nocount off   I don t put it in proc form since I don t want to maintain it across hundreds of DBs and it s really for ad-hoc work anyway   Please feel free to comment on bug-fixes

User · Answer

Database client tools  like DBeaver or phpMyAdmin  often support means of fulltext search through entire database

User · Answer

I optimized Allain Lalonde answer  https   stackoverflow com a 436676 412368   Numeric values are still supported  Should be roughly 4-5 times faster  1 03 vs 4 30   tested on a desktop with a 7GB database  http   developer azurewebsites net 2015 01 mssql-searchalltables   IF OBJECT ID   dbo SearchAllTables    P   IS NOT NULL      DROP PROCEDURE dbo SearchAllTables  GO  CREATE PROC SearchAllTables         SearchStr nvarchar 100    AS BEGIN  -- 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 -- Customized and modified  2014-01-21 -- Tested on  SQL Server 2008 R2  DECLARE  Results TABLE ColumnName nvarchar 370   ColumnValue nvarchar 3630    SET NOCOUNT ON  DECLARE  TableName nvarchar 256  DECLARE  ColumnName nvarchar 128  DECLARE  DataType nvarchar 128   DECLARE  SearchStr2 nvarchar 110  DECLARE  SearchDecimal decimal 38 19  DECLARE  Query nvarchar 4000  SET  SearchStr2   QUOTENAME        SearchStr              SET  SearchDecimal   CASE WHEN ISNUMERIC  SearchStr    1 THEN CONVERT decimal 38 19    SearchStr  ELSE NULL END PRINT   SearchStr2       SearchStr2 PRINT   SearchDecimal      CAST  SearchDecimal AS nvarchar   SET  TableName      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                       DATA TYPE             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    bigint    tinyint    numeric    decimal                   AND QUOTENAME COLUMN NAME   gt   ColumnName                   SET  DataType                         SELECT DATA TYPE             FROM    INFORMATION SCHEMA COLUMNS             WHERE       TABLE SCHEMA      PARSENAME  TableName  2                  AND TABLE NAME    PARSENAME  TableName  1                  AND QUOTENAME COLUMN NAME     ColumnName                   PRINT  TableName          ColumnName           DataType                IF  ColumnName IS NOT NULL         BEGIN             IF  DataType IN   int    bigint    tinyint    numeric    decimal               BEGIN                 IF  SearchDecimal IS NOT NULL                 BEGIN                     SET  Query    SELECT        TableName          ColumnName        LEFT CAST      ColumnName     AS nvarchar 110    3630                                        FROM      TableName      NOLOCK                                         WHERE      ColumnName           CAST  SearchDecimal AS nvarchar                      PRINT           Query                     INSERT INTO  Results                     EXEC   Query                  END             END             ELSE             BEGIN                 SET  Query    SELECT        TableName          ColumnName        LEFT      ColumnName      3630                                    FROM      TableName      NOLOCK                                     WHERE      ColumnName     LIKE      SearchStr2                 PRINT           Query                 INSERT INTO  Results                 EXEC   Query              END         END     END  END  SELECT ColumnName  ColumnValue FROM  Results END

User · Answer

I optimized Allain Lalonde answer  https   stackoverflow com a 436676 412368   Numeric values are still supported  Should be roughly 4-5 times faster  1 03 vs 4 30   tested on a desktop with a 7GB database  http   developer azurewebsites net 2015 01 mssql-searchalltables   IF OBJECT ID   dbo SearchAllTables    P   IS NOT NULL      DROP PROCEDURE dbo SearchAllTables  GO  CREATE PROC SearchAllTables         SearchStr nvarchar 100    AS BEGIN  -- 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 -- Customized and modified  2014-01-21 -- Tested on  SQL Server 2008 R2  DECLARE  Results TABLE ColumnName nvarchar 370   ColumnValue nvarchar 3630    SET NOCOUNT ON  DECLARE  TableName nvarchar 256  DECLARE  ColumnName nvarchar 128  DECLARE  DataType nvarchar 128   DECLARE  SearchStr2 nvarchar 110  DECLARE  SearchDecimal decimal 38 19  DECLARE  Query nvarchar 4000  SET  SearchStr2   QUOTENAME        SearchStr              SET  SearchDecimal   CASE WHEN ISNUMERIC  SearchStr    1 THEN CONVERT decimal 38 19    SearchStr  ELSE NULL END PRINT   SearchStr2       SearchStr2 PRINT   SearchDecimal      CAST  SearchDecimal AS nvarchar   SET  TableName      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                       DATA TYPE             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    bigint    tinyint    numeric    decimal                   AND QUOTENAME COLUMN NAME   gt   ColumnName                   SET  DataType                         SELECT DATA TYPE             FROM    INFORMATION SCHEMA COLUMNS             WHERE       TABLE SCHEMA      PARSENAME  TableName  2                  AND TABLE NAME    PARSENAME  TableName  1                  AND QUOTENAME COLUMN NAME     ColumnName                   PRINT  TableName          ColumnName           DataType                IF  ColumnName IS NOT NULL         BEGIN             IF  DataType IN   int    bigint    tinyint    numeric    decimal               BEGIN                 IF  SearchDecimal IS NOT NULL                 BEGIN                     SET  Query    SELECT        TableName          ColumnName        LEFT CAST      ColumnName     AS nvarchar 110    3630                                        FROM      TableName      NOLOCK                                         WHERE      ColumnName           CAST  SearchDecimal AS nvarchar                      PRINT           Query                     INSERT INTO  Results                     EXEC   Query                  END             END             ELSE             BEGIN                 SET  Query    SELECT        TableName          ColumnName        LEFT      ColumnName      3630                                    FROM      TableName      NOLOCK                                     WHERE      ColumnName     LIKE      SearchStr2                 PRINT           Query                 INSERT INTO  Results                 EXEC   Query              END         END     END  END  SELECT ColumnName  ColumnValue FROM  Results END

User · Answer

I was looking for a just a numeric value   6 84 - using the other answers here I was able to limit my search to this Declare  sourceTable Table id INT NOT NULL IDENTITY PRIMARY KEY  table name varchar 1000   column name varchar 1000   Declare  resultsTable Table id INT NOT NULL IDENTITY PRIMARY KEY  table name varchar 1000    Insert into  sourceTable table name  column name  select schema name t schema id          t name as table   c name as column name from sys columns c join sys tables t on t object id   c object id where type name user type id  in   decimal    numeric    smallmoney    money    float    real   order by table   c column id   DECLARE db cursor CURSOR FOR Select table name  column name from  sourceTable DECLARE  mytablename VARCHAR 1000   DECLARE  mycolumnname VARCHAR 1000    OPEN db cursor  FETCH NEXT FROM db cursor INTO  mytablename   mycolumnname  WHILE    FETCH STATUS   0 BEGIN     Insert into  ResultsTable table name      EXEC  SELECT        mytablename          mycolumnname        FROM      mytablename      NOLOCK            WHERE      mycolumnname     6 84       FETCH NEXT FROM db cursor INTO  mytablename   mycolumnname   END  CLOSE db cursor  DEALLOCATE db cursor  Select Distinct table name  from  ResultsTable

User · Answer

Suppose if you want to get all the table with name a column name contain logintime in the database MyDatabase below is the code sample      use MyDatabase      SELECT t name AS table name      SCHEMA NAME schema id  AS schema name      c name AS column name     FROM sys tables AS t     INNER JOIN sys columns c ON t OBJECT ID   c OBJECT ID     WHERE c name LIKE   logintime       ORDER BY schema name  table name

User · Answer

Thanks for the really useful script   You may need to add the following modification to the code if your tables have non-convertable fields   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 NOT IN   text    image    ntext                                AND QUOTENAME COLUMN NAME   gt   ColumnName         Chris

User · Answer

You might need to build an inverted index for your database  It is assured to be pretty fast

User · Answer

Another way using JOIN and CURSOR   USE My Database   -- Store results in a local temp table so that   I m using a -- local temp table so that I can access it in SP EXECUTESQL  create table  tmp       tbl nvarchar max       col nvarchar max       val nvarchar max         declare  tbl nvarchar max   declare  col nvarchar max   declare  q nvarchar max   declare  search nvarchar max     my search key    -- Create a cursor on all columns in the database declare c cursor for SELECT tbls TABLE NAME  cols COLUMN NAME  FROM INFORMATION SCHEMA TABLES AS tbls JOIN INFORMATION SCHEMA COLUMNS AS cols ON tbls TABLE NAME   cols TABLE NAME  -- For each table and column pair  see if the search value exists  open c fetch next from c into  tbl   col while   FETCH STATUS   0 begin     -- Look for the search key in current table column and if found add it to the results      SET  q    INSERT INTO  tmp SELECT        tbl               col             col     FROM      tbl     WHERE      col     LIKE         search             EXEC SP EXECUTESQL  q     fetch next from c into  tbl   col end close c deallocate c  -- Get results select   from  tmp  -- Remove local temp table  drop table  tmp

User · Answer

-- exec pSearchAllTables  M54    ALTER PROC pSearchAllTables   SearchStr NVARCHAR 100   AS BEGIN     -- A procedure to search all tables in a database for a value     -- Note  Use   or   for wildcard      DECLARE           Results TABLE  Schema Table ColumnName  NVARCHAR 370   ColumnValue NVARCHAR 3630        SET NOCOUNT ON      DECLARE           TableName NVARCHAR 256                  ColumnName NVARCHAR 128                  SearchStr2 NVARCHAR 110    QUOTENAME REPLACE  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                           AND QUOTENAME COLUMN NAME   gt   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           Schema Table ColumnName            ColumnValue      FROM  Results     GROUP BY           Schema Table ColumnName            ColumnValue   END

User · Answer

-- exec pSearchAllTables  M54    ALTER PROC pSearchAllTables   SearchStr NVARCHAR 100   AS BEGIN     -- A procedure to search all tables in a database for a value     -- Note  Use   or   for wildcard      DECLARE           Results TABLE  Schema Table ColumnName  NVARCHAR 370   ColumnValue NVARCHAR 3630        SET NOCOUNT ON      DECLARE           TableName NVARCHAR 256                  ColumnName NVARCHAR 128                  SearchStr2 NVARCHAR 110    QUOTENAME REPLACE  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                           AND QUOTENAME COLUMN NAME   gt   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           Schema Table ColumnName            ColumnValue      FROM  Results     GROUP BY           Schema Table ColumnName            ColumnValue   END

User · Answer

If you need to run such search only once then you can probably go with any of the scripts already shown in other answers  But otherwise  I   d recommend using ApexSQL Search for this  It   s a free SSMS addin and it really saved me a lot of time   Before running any of the scripts you should customize it based on the data type you want to search  If you know you are searching for datetime column then there is no need to search through nvarchar columns  This will speed up all of the queries above

User · Answer

I wrote once a tool for myself to do exactly that thing   a7 SqlTools  It s free and open-source   github link

User · Answer

Here  very sweet and small solution   1  create a store procedure   create procedure get table  find str varchar 50  as  begin   declare  col name varchar 500    tab name varchar 500     declare  find tab TABLE table name varchar 100   column name varchar 100       DECLARE tab col cursor for    select C name as  col name   T name as tab name   from sys tables as T   left outer join sys columns as C on  C object id T object id   left outer join sys types as TP on  C system type id TP system type id   where type  U     and TP name in  text   ntext   varchar   char   nvarchar   nchar       open tab col   fetch next from tab col into  col name   tab name    while   FETCH STATUS   0   begin             insert into  find tab      exec  select         tab name              col name       from      tab name          where      col name            find str       group by           col name     having count    gt 0         fetch next from tab col into  col name   tab name    end   CLOSE tab col      DEALLOCATE tab col     select table name  column name from  find tab   end                               2  call procedure by calling store procedure  exec get table  serach string

User · Answer

Thanks for the really useful script   You may need to add the following modification to the code if your tables have non-convertable fields   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 NOT IN   text    image    ntext                                AND QUOTENAME COLUMN NAME   gt   ColumnName         Chris

User · Answer

You might need to build an inverted index for your database  It is assured to be pretty fast

User · Answer

I have a solution from a while ago that I kept improving  Also searches within XML columns if told to do so  or searches integer values if providing a integer only string      Reto Egeter  fullparam wordpress com     DECLARE  SearchStrTableName nvarchar 255    SearchStrColumnName nvarchar 255    SearchStrColumnValue nvarchar 255    SearchStrInXML bit   FullRowResult bit   FullRowResultRows int SET  SearchStrColumnValue     searchthis      use LIKE syntax    SET  FullRowResult   1 SET  FullRowResultRows   3 SET  SearchStrTableName   NULL    NULL for all tables  uses LIKE syntax    SET  SearchStrColumnName   NULL    NULL for all columns  uses LIKE syntax    SET  SearchStrInXML   0    Searching XML data may be slow     IF OBJECT ID  tempdb   Results   IS NOT NULL DROP TABLE  Results CREATE TABLE  Results  TableName nvarchar 128   ColumnName nvarchar 128   ColumnValue nvarchar max  ColumnType nvarchar 20    SET NOCOUNT ON  DECLARE  TableName nvarchar 256        ColumnName nvarchar 128   ColumnType nvarchar 20    QuotedSearchStrColumnValue nvarchar 110    QuotedSearchStrColumnName nvarchar 110  SET  QuotedSearchStrColumnValue   QUOTENAME  SearchStrColumnValue       DECLARE  ColumnNameTable TABLE  COLUMN NAME nvarchar 128  DATA TYPE nvarchar 20    WHILE  TableName IS NOT NULL BEGIN SET  TableName     SELECT MIN QUOTENAME TABLE SCHEMA          QUOTENAME TABLE NAME   FROM INFORMATION SCHEMA TABLES WHERE TABLE TYPE    BASE TABLE  AND TABLE NAME LIKE COALESCE  SearchStrTableName TABLE NAME  AND QUOTENAME TABLE SCHEMA          QUOTENAME TABLE NAME   gt   TableName AND OBJECTPROPERTY OBJECT ID QUOTENAME TABLE SCHEMA          QUOTENAME TABLE NAME     IsMSShipped     0   IF  TableName IS NOT NULL BEGIN DECLARE  sql VARCHAR MAX  SET  sql    SELECT QUOTENAME COLUMN NAME  DATA TYPE FROM INFORMATION SCHEMA COLUMNS WHERE TABLE SCHEMA   PARSENAME        TableName        2  AND TABLE NAME   PARSENAME        TableName        1  AND DATA TYPE IN      CASE WHEN ISNUMERIC REPLACE REPLACE REPLACE REPLACE REPLACE  SearchStrColumnValue                                  -         1 THEN    tinyint     int     smallint     bigint     numeric     decimal     smallmoney     money     ELSE    END      char     varchar     nchar     nvarchar     timestamp     uniqueidentifier      CASE  SearchStrInXML WHEN 1 THEN     xml    ELSE    END      AND COLUMN NAME LIKE COALESCE     CASE WHEN  SearchStrColumnName IS NULL THEN  NULL  ELSE         SearchStrColumnName        END     COLUMN NAME   INSERT INTO  ColumnNameTable EXEC   sql  WHILE EXISTS  SELECT TOP 1 COLUMN NAME FROM  ColumnNameTable  BEGIN PRINT  ColumnName SELECT TOP 1  ColumnName   COLUMN NAME  ColumnType   DATA TYPE FROM  ColumnNameTable SET  sql    SELECT        TableName              ColumnName           CASE  ColumnType WHEN  xml  THEN  LEFT CAST      ColumnName     AS nvarchar MAX    4096      WHEN  timestamp  THEN  master dbo fn varbintohexstr     ColumnName          ELSE  LEFT      ColumnName      4096      END    ColumnType       FROM      TableName      NOLOCK        WHERE     CASE  ColumnType WHEN  xml  THEN  CAST      ColumnName     AS nvarchar MAX    WHEN  timestamp  THEN  master dbo fn varbintohexstr     ColumnName       ELSE  ColumnName END     LIKE      QuotedSearchStrColumnValue INSERT INTO  Results EXEC  sql  IF   ROWCOUNT  gt  0 IF  FullRowResult   1 BEGIN SET  sql    SELECT TOP     CAST  FullRowResultRows AS VARCHAR 3              TableName       AS  TableFound         ColumnName       AS  ColumnFound    FullRow gt    AS  FullRow gt          FROM      TableName      NOLOCK        WHERE     CASE  ColumnType WHEN  xml  THEN  CAST      ColumnName     AS nvarchar MAX    WHEN  timestamp  THEN  master dbo fn varbintohexstr     ColumnName       ELSE  ColumnName END     LIKE      QuotedSearchStrColumnValue EXEC  sql  END DELETE FROM  ColumnNameTable WHERE COLUMN NAME    ColumnName END  END END SET NOCOUNT OFF  SELECT TableName  ColumnName  ColumnValue  ColumnType  COUNT    AS Count FROM  Results GROUP BY TableName  ColumnName  ColumnValue  ColumnType   Source  http   fullparam wordpress com 2012 09 07 fck-it-i-am-going-to-search-all-tables-all-collumns

User · Answer

I have a solution from a while ago that I kept improving  Also searches within XML columns if told to do so  or searches integer values if providing a integer only string      Reto Egeter  fullparam wordpress com     DECLARE  SearchStrTableName nvarchar 255    SearchStrColumnName nvarchar 255    SearchStrColumnValue nvarchar 255    SearchStrInXML bit   FullRowResult bit   FullRowResultRows int SET  SearchStrColumnValue     searchthis      use LIKE syntax    SET  FullRowResult   1 SET  FullRowResultRows   3 SET  SearchStrTableName   NULL    NULL for all tables  uses LIKE syntax    SET  SearchStrColumnName   NULL    NULL for all columns  uses LIKE syntax    SET  SearchStrInXML   0    Searching XML data may be slow     IF OBJECT ID  tempdb   Results   IS NOT NULL DROP TABLE  Results CREATE TABLE  Results  TableName nvarchar 128   ColumnName nvarchar 128   ColumnValue nvarchar max  ColumnType nvarchar 20    SET NOCOUNT ON  DECLARE  TableName nvarchar 256        ColumnName nvarchar 128   ColumnType nvarchar 20    QuotedSearchStrColumnValue nvarchar 110    QuotedSearchStrColumnName nvarchar 110  SET  QuotedSearchStrColumnValue   QUOTENAME  SearchStrColumnValue       DECLARE  ColumnNameTable TABLE  COLUMN NAME nvarchar 128  DATA TYPE nvarchar 20    WHILE  TableName IS NOT NULL BEGIN SET  TableName     SELECT MIN QUOTENAME TABLE SCHEMA          QUOTENAME TABLE NAME   FROM INFORMATION SCHEMA TABLES WHERE TABLE TYPE    BASE TABLE  AND TABLE NAME LIKE COALESCE  SearchStrTableName TABLE NAME  AND QUOTENAME TABLE SCHEMA          QUOTENAME TABLE NAME   gt   TableName AND OBJECTPROPERTY OBJECT ID QUOTENAME TABLE SCHEMA          QUOTENAME TABLE NAME     IsMSShipped     0   IF  TableName IS NOT NULL BEGIN DECLARE  sql VARCHAR MAX  SET  sql    SELECT QUOTENAME COLUMN NAME  DATA TYPE FROM INFORMATION SCHEMA COLUMNS WHERE TABLE SCHEMA   PARSENAME        TableName        2  AND TABLE NAME   PARSENAME        TableName        1  AND DATA TYPE IN      CASE WHEN ISNUMERIC REPLACE REPLACE REPLACE REPLACE REPLACE  SearchStrColumnValue                                  -         1 THEN    tinyint     int     smallint     bigint     numeric     decimal     smallmoney     money     ELSE    END      char     varchar     nchar     nvarchar     timestamp     uniqueidentifier      CASE  SearchStrInXML WHEN 1 THEN     xml    ELSE    END      AND COLUMN NAME LIKE COALESCE     CASE WHEN  SearchStrColumnName IS NULL THEN  NULL  ELSE         SearchStrColumnName        END     COLUMN NAME   INSERT INTO  ColumnNameTable EXEC   sql  WHILE EXISTS  SELECT TOP 1 COLUMN NAME FROM  ColumnNameTable  BEGIN PRINT  ColumnName SELECT TOP 1  ColumnName   COLUMN NAME  ColumnType   DATA TYPE FROM  ColumnNameTable SET  sql    SELECT        TableName              ColumnName           CASE  ColumnType WHEN  xml  THEN  LEFT CAST      ColumnName     AS nvarchar MAX    4096      WHEN  timestamp  THEN  master dbo fn varbintohexstr     ColumnName          ELSE  LEFT      ColumnName      4096      END    ColumnType       FROM      TableName      NOLOCK        WHERE     CASE  ColumnType WHEN  xml  THEN  CAST      ColumnName     AS nvarchar MAX    WHEN  timestamp  THEN  master dbo fn varbintohexstr     ColumnName       ELSE  ColumnName END     LIKE      QuotedSearchStrColumnValue INSERT INTO  Results EXEC  sql  IF   ROWCOUNT  gt  0 IF  FullRowResult   1 BEGIN SET  sql    SELECT TOP     CAST  FullRowResultRows AS VARCHAR 3              TableName       AS  TableFound         ColumnName       AS  ColumnFound    FullRow gt    AS  FullRow gt          FROM      TableName      NOLOCK        WHERE     CASE  ColumnType WHEN  xml  THEN  CAST      ColumnName     AS nvarchar MAX    WHEN  timestamp  THEN  master dbo fn varbintohexstr     ColumnName       ELSE  ColumnName END     LIKE      QuotedSearchStrColumnValue EXEC  sql  END DELETE FROM  ColumnNameTable WHERE COLUMN NAME    ColumnName END  END END SET NOCOUNT OFF  SELECT TableName  ColumnName  ColumnValue  ColumnType  COUNT    AS Count FROM  Results GROUP BY TableName  ColumnName  ColumnValue  ColumnType   Source  http   fullparam wordpress com 2012 09 07 fck-it-i-am-going-to-search-all-tables-all-collumns

User · Answer

Based on bnkdev s answer I modified Narayana s Code to search all columns even numeric ones   It ll run slower  but this version actually finds all matches not just those found in text columns   I can t thank this guy enough  Saved me days of searching by hand   CREATE PROC SearchAllTables     SearchStr nvarchar 100    AS BEGIN  -- 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 and SQL Server 2000 -- Date modified  28th July 2002 22 50 GMT   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 QUOTENAME COLUMN NAME   gt   ColumnName                    IF  ColumnName IS NOT NULL         BEGIN             INSERT INTO  Results             EXEC                                SELECT        TableName          ColumnName        LEFT CONVERT varchar max        ColumnName       3630                   FROM      TableName      NOLOCK                        WHERE CONVERT varchar max        ColumnName      LIKE      SearchStr2                       END     END  END  SELECT ColumnName  ColumnValue FROM  Results END

User · Answer

Suppose if you want to get all the table with name a column name contain logintime in the database MyDatabase below is the code sample      use MyDatabase      SELECT t name AS table name      SCHEMA NAME schema id  AS schema name      c name AS column name     FROM sys tables AS t     INNER JOIN sys columns c ON t OBJECT ID   c OBJECT ID     WHERE c name LIKE   logintime       ORDER BY schema name  table name

User · Answer

This might help you  - from Narayana Vyas  It searches all columns of all tables in a given database  I have used it before and it works   This is the Stored Proc from the above link - the only change I made was substituting the temp table for a table variable so you don t have to remember to drop it each time   CREATE PROC SearchAllTables        SearchStr nvarchar 100    AS BEGIN  -- 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 and SQL Server 2000 -- Date modified  28th July 2002 22 50 GMT  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                   AND QUOTENAME COLUMN NAME   gt   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

User · Answer

Database client tools  like DBeaver or phpMyAdmin  often support means of fulltext search through entire database

User · Answer

This is my independent take on this question that I use for my own work   It works in SQL2000 and greater  allows wildcards  column filtering  and will search most of the normal data types   A pseudo-code description could be select   from   where any like  foo   -------------------------------------------------------------------------------- -- Search all columns in all tables in a database for a string  -- Does not search  image  sql variant or user-defined types  -- Exact search always for money and smallmoney  no wildcards for matching these  -------------------------------------------------------------------------------- declare  SearchTerm nvarchar 4000  -- Can be max for SQL2005  declare  ColumnName sysname  -------------------------------------------------------------------------------- -- SET THESE  -------------------------------------------------------------------------------- set  SearchTerm   N foo  -- Term to be searched for  wildcards okay set  ColumnName   N   -- Use to restrict the search to certain columns  wildcards okay  null or empty string for all cols -------------------------------------------------------------------------------- -- END SET --------------------------------------------------------------------------------  set nocount on  declare  TabCols table         id int not null primary key identity       table schema sysname not null       table name sysname not null       column name sysname not null       data type sysname not null   insert into  TabCols  table schema  table name  column name  data type      select t TABLE SCHEMA  c TABLE NAME  c COLUMN NAME  c DATA TYPE     from INFORMATION SCHEMA TABLES t         join INFORMATION SCHEMA COLUMNS c on t TABLE SCHEMA   c TABLE SCHEMA             and t TABLE NAME   c TABLE NAME     where 1   1         and t TABLE TYPE    base table          and c DATA TYPE not in   image    sql variant           and c COLUMN NAME like case when len  ColumnName   gt  0 then  ColumnName else     end     order by c TABLE NAME  c ORDINAL POSITION  declare        table schema sysname        table name sysname        column name sysname        data type sysname        exists nvarchar 4000  -- Can be max for SQL2005         sql nvarchar 4000  -- Can be max for SQL2005         where nvarchar 4000  -- Can be max for SQL2005         run nvarchar 4000  -- Can be max for SQL2005   while exists  select null from  TabCols  begin      select top 1            table schema   table schema            table name   table name            exists    select null from      table schema           table name      where 1   0             sql    select             table schema           table name             as TABLE NAME    from      table schema           table name      where 1   0             where          from  TabCols     order by id      while exists  select null from  TabCols where table schema    table schema and table name    table name  begin          select top 1                column name   column name                data type   data type         from  TabCols         where table schema    table schema             and table name    table name         order by id          -- Special case for money         if  data type in   money    smallmoney   begin             if isnumeric  SearchTerm    1 begin                 set  where    where     or       column name        cast        SearchTerm       as      data type       -- could also cast the column as varchar for wildcards             end         end         -- Special case for xml         else if  data type    xml  begin             set  where    where     or cast       column name      as nvarchar max   like        SearchTerm                end         -- Special case for date         else if  data type in   date    datetime    datetime2    datetimeoffset    smalldatetime    time   begin             set  where    where     or convert nvarchar 50         column name       121  like        SearchTerm                end         -- Search all other types         else begin             set  where    where     or       column name      like        SearchTerm                end          delete from  TabCols where table schema    table schema and table name    table name and column name    column name      end      set  run    if exists      exists    where      begin      sql    where     print        table name       end      print  run     exec sp executesql  run  end  set nocount off   I don t put it in proc form since I don t want to maintain it across hundreds of DBs and it s really for ad-hoc work anyway   Please feel free to comment on bug-fixes

User · Answer

If you have phpMyAdmin installed use its Search feature   Select your DataBase   Be sure you do have selected DataBase  not a table  otherwise you ll get a completely different search dialog    Click Search tab  List item Choose the search term you want   Choose the tables to search

[sql-server] Find a value anywhere in a database

Examples related to sql-server