Search for a string in all tables rows and columns of a DB

Question

I am lost in a big database and I am not able to find where the data I get comes from  I was wondering if it is possible with SQL Server 2005 to search for a string in all tables  rows and columns of a database   Does anybody has an idea if it is possible and how

User · Answer

I think this can be an easiest way to find a string in all rows of your database -without using cursors and FOR XML-.

CREATE PROCEDURE SPFindAll (@find VARCHAR(max) = '')
AS
BEGIN
    SET NOCOUNT ON;
    --
    DECLARE @query VARCHAR(max) = ''

    SELECT  @query = @query + 
            CASE 
                WHEN @query = '' THEN '' 
                ELSE ' UNION ALL '
            END +
            'SELECT ''' + s.name + ''' As schemaName, ''' + t.name + ''' As tableName, ''' + c.name + ''' As ColumnName, [' + c.name + '] COLLATE DATABASE_DEFAULT As [Data] FROM [' + s.name + '].[' + t.name + '] WHERE [' + c.name + '] Like ''%' + @find + '%'''
    FROM 
        sys.schemas s
        INNER JOIN
        sys.tables t ON s.[schema_id] = t.[schema_id]
        INNER JOIN 
        sys.columns c ON t.[object_id] = c.[object_id]
        INNER JOIN
        sys.types ty ON c.user_type_id = ty.user_type_id
    WHERE
        ty.name LIKE '%char'

    EXEC(@query)
END

By creating this stored procedure you can run it for any string you want to find like this:

EXEC SPFindAll 'Hello World'

The result will be like this:

schemaName | tableName | columnName | Data
-----------+-----------+------------+-----------------------
schema1    | Table1    | Column1    | Hello World
schema1    | Table1    | Column1    | Hello World!
schema1    | Table2    | Column1    | I say "Hello World".
schema1    | Table2    | Column2    | Hello World

User · Answer

I   d suggest you find yourself a 3rd party tool for this such as ApexSQL Search  there are probably others out there too but I use this one because it   s free      If you really want to go the SQL way you can try using stored procedure created by  Sorna Kumar Muthuraj     copied code is below  Just execute this stored procedure for all tables in your schema  easy with dynamics SQL   CREATE PROCEDURE SearchTables    Tablenames VARCHAR 500     SearchStr NVARCHAR 60     GenerateSQLOnly Bit   0  AS           Parameters and usage        Tablenames        -- Provide a single table name or multiple table name with comma seperated                            If left blank   it will check for all the tables in the database       SearchStr        -- Provide the search string  Use the     to coin the search                            EX   X --- will give data staring with X                                X--- will give data ending with X                                X --- will give data containig  X       GenerateSQLOnly -- Provide 1 if you only want to generate the SQL statements without seraching the database                            By default it is 0 and it will search        Samples         1  To search data in a table           EXEC SearchTables  Tablenames    T1                              SearchStr      TEST             The above sample searches in table T1 with string containing TEST        2  To search in a multiple table           EXEC SearchTables  Tablenames    T2                              SearchStr      TEST             The above sample searches in tables T1  amp  T2 with string containing TEST        3  To search in a all table           EXEC SearchTables  Tablenames                                   SearchStr      TEST             The above sample searches in all table with string containing TEST        4  Generate the SQL for the Select statements           EXEC SearchTables  Tablenames           T1                              SearchStr            TEST                               GenerateSQLOnly      1            SET NOCOUNT ON       DECLARE  CheckTableNames Table             Tablename sysname              DECLARE  SQLTbl TABLE              Tablename        SYSNAME       WHEREClause    VARCHAR MAX        SQLStatement   VARCHAR MAX        Execstatus        BIT               DECLARE  sql VARCHAR MAX       DECLARE  tmpTblname sysname       IF LTRIM RTRIM  Tablenames   IN                BEGIN           INSERT INTO  CheckTableNames          SELECT Name            FROM sys tables      END      ELSE      BEGIN           SELECT  sql    SELECT       REPLACE  Tablenames         UNION SELECT                       INSERT INTO  CheckTableNames          EXEC  sql        END       INSERT INTO  SQLTbl        Tablename WHEREClause       SELECT SCh name         ST NAME                                  SELECT       SC name           LIKE        SearchStr       OR     CHAR 10                     FROM SYS columns SC                    JOIN SYS types STy                      ON STy system type id   SC system type id                     AND STy user type id  SC user type id                   WHERE STY name in   varchar   char   nvarchar   nchar                       AND SC object id   ST object id                   ORDER BY SC name                  FOR XML PATH                           FROM  SYS tables ST        JOIN  CheckTableNames chktbls                  ON chktbls Tablename   ST name         JOIN SYS schemas SCh          ON ST schema id   SCh schema id       WHERE ST name  lt  gt   SearchTMP         GROUP BY ST object id  SCh name         ST NAME           UPDATE  SQLTbl           SET SQLStatement    SELECT   INTO SearchTMP FROM     Tablename     WHERE     substring WHEREClause 1 len WHEREClause -5          DELETE FROM  SQLTbl         WHERE WHEREClause IS NULL       WHILE EXISTS  SELECT 1 FROM  SQLTbl WHERE ISNULL Execstatus  0    0       BEGIN           SELECT TOP 1  tmpTblname   Tablename    sql   SQLStatement            FROM  SQLTbl            WHERE ISNULL Execstatus  0    0              IF  GenerateSQLOnly   0           BEGIN               IF OBJECT ID  SearchTMP   U   IS NOT NULL                  DROP TABLE SearchTMP              EXEC   SQL                IF EXISTS SELECT 1 FROM SearchTMP               BEGIN                  SELECT Tablename  tmpTblname   FROM SearchTMP              END            END           ELSE           BEGIN               PRINT REPLICATE  -  100                PRINT  tmpTblname               PRINT REPLICATE  -  100                PRINT replace  sql  INTO SearchTMP                END            UPDATE  SQLTbl              SET Execstatus   1            WHERE Tablename    tmpTblname       END       SET NOCOUNT OFF   go

User · Answer

Other answers posted already may work equally well or better  but I haven t used them   However  the following SQL I have used  and it really helped me out when I was trying to reverse-engineer a big system with a huge  and very unorganzied  SQL Server database   This isn t my code   I wish I could credit the original author  but I can t find the link to the article anymore     Use  go  declare  SearchChar varchar 8000  Set  SearchChar    -- Like  A     11 11 2006   declare  CMDMain varchar 8000    CMDMainCount varchar 8000   CMDJoin varchar 8000  declare  ColumnName varchar 100   TableName varchar 100   declare dbTable cursor for  SELECT  Distinct b Name as TableName FROM  sysobjects b WHERE  b type  u  and b Name   dtproperties  order by b name open dbTable fetch next from dbTable into  TableName  WHILE   FETCH STATUS   0 BEGIN declare db cursor for  SELECT  c Name as ColumnName FROM  sysobjects b  syscolumns c WHERE  C id   b id and b type  u  and b Name    TableName order by b name open db fetch next from db into  ColumnName set  CMDMain    SELECT     char 39     TableName   char 39      as TableName            TableName        FROM       TableName          WHERE   set  CMDMainCount    SELECT Count    FROM       TableName      Where   Set  CMDJoin      WHILE   FETCH STATUS   0 BEGIN set  CMDJoin    CMDJoin    Convert varchar 5000       ColumnName       like     char 39     SearchChar   char 39      OR    fetch next from db into  ColumnName end close db deallocate db  Set  CMDMainCount    If      CMDMainCount   Left  CMDJoin  len  CMDJoin  - 3        0 Begin   Set  CMDMain    CMDMainCount    CMDMain   Left  CMDJoin  len  CMDJoin  - 3  Set  CMDMain    CMDMain     End    Print  CMDMain  exec   CMDMain  fetch next from dbTable into  TableName end close dbTable deallocate dbTable

User · Answer

Or  you can use my query here  should be simpler then having to create sProcs for each DB you want to search  FullParam SQL Blog     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

User · Answer

This uses no cursors or anything like that  just one dynamic query    Also note that this uses LIKE  Since that happened to be what I needed  It works for all schemas  all tables and only query s those columns that are NVARCHAR or VARCHAR even if they have UDDT    DECLARE  searchstring  NVARCHAR 255  SET  searchstring     searchstring    DECLARE  sql NVARCHAR max   SELECT  sql   STUFF       SELECT   UNION ALL SELECT       TABLE NAME       AS tablename        COLUMN NAME       AS columnname      COLUMN NAME     AS valuename           FROM     TABLE SCHEMA         TABLE NAME          WHERE     COLUMN NAME     LIKE        searchstring            FROM INFORMATION SCHEMA COLUMNS      WHERE DATA TYPE in   nvarchar    varchar       FOR XML PATH        1  11       EXEC  sql    The output gives you the table  column and value  Time to execute on a small database was  3 seconds  had about 3000 results

User · Answer

I adapted a script originally written by Narayana Vyas Kondreddi in 2002  I changed the where clause to check text ntext fields as well  by using patindex rather than like  I also changed the results table slightly  Unreasonably  I changed variable names  and aligned as I prefer  no disrespect to Mr  Kondretti   The user may want to change the data types searched  I used a global table to allow querying mid-processing  but a permanent table might be a smarter way to go      original script by Narayana Vyas Kondreddi  2002       adapted by Oliver Holloway  2009        these lines can be replaced by use of input parameter for a proc    declare  search string varchar 1000   set  search string    what you are searching for       create results table    create table   string locations     table name varchar 1000     field name varchar 1000     field value varchar 8000         special settings    set nocount on      declare variables    declare    table name varchar 1000      field name varchar 1000       variable settings    set  table name        set  search string   QUOTENAME        search string                  for each table    while  table name is not null begin    set  field name        set  table name         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   table name and       OBJECTPROPERTY OBJECT ID QUOTENAME table schema          QUOTENAME table name     IsMSShipped     0           for each string-ish field      while   table name is not null  and   field name is not null    begin     set  field name           select MIN QUOTENAME column name         from INFORMATION SCHEMA COLUMNS       where          table schema      PARSENAME  table name  2  and         table name    PARSENAME  table name  1  and         data type in   char    varchar    nchar    nvarchar    text    ntext   and         QUOTENAME column name   gt   field name               search that field for the string supplied        if  field name is not null     begin       insert into   string locations       exec           select        table name              field name            field name             from      table name      nolock               where patindex      search string          field name       gt  0      patindex works with char  amp  text                end         end     end       return results    select table name  field name  field value from   string locations  nolock       drop temp table    --drop table   string locations

User · Answer

NLwino  yery good query with a few errors for keyword usage  I had to modify it a little to wrap the keywords with     and also look char and ntext columns        DECLARE  searchstring  NVARCHAR 255      SET  searchstring     WDB1014        DECLARE  sql NVARCHAR max       SELECT  sql   STUFF         SELECT   UNION ALL SELECT       TABLE NAME       AS tbl        COLUMN NAME       AS col       COLUMN NAME      AS val               FROM     TABLE SCHEMA          TABLE NAME               WHERE      COLUMN NAME      LIKE        searchstring                FROM INFORMATION SCHEMA COLUMNS          WHERE DATA TYPE in   nvarchar    varchar    char    ntext                     FOR XML PATH                     1  11           Exec   sql    I ran it on 2 5 GB database and it came back in 51 seconds

User · Answer

If you are  getting data  from an application  the sensible thing would be to use the profiler and profile the database while running the application   Trace it  then search the results for that string

User · Answer

This code should do it in SQL 2005  but a few caveats    It is RIDICULOUSLY slow  I tested it on a small database that I have with only a handful of tables and it took many minutes to complete  If your database is so big that you can t understand it then this will probably be unusable anyway  I wrote this off the cuff  I didn t put in any error handling and there might be some other sloppiness especially since I don t use cursors often  For example  I think there s a way to refresh the columns cursor instead of closing deallocating recreating it every time    If you can t understand the database or don t know where stuff is coming from  then you should probably find someone who does  Even if you can find where the data is  it might be duplicated somewhere or there might be other aspects of the database that you don t understand  If no one in your company understands the database then you re in a pretty big mess   DECLARE      search string  VARCHAR 100        table name     SYSNAME       table schema   SYSNAME       column name    SYSNAME       sql string     VARCHAR 2000   SET  search string    Test   DECLARE tables cur CURSOR FOR SELECT TABLE SCHEMA  TABLE NAME FROM INFORMATION SCHEMA TABLES WHERE TABLE TYPE    BASE TABLE   OPEN tables cur  FETCH NEXT FROM tables cur INTO  table schema   table name  WHILE    FETCH STATUS   0  BEGIN     DECLARE columns cur CURSOR FOR SELECT COLUMN NAME FROM INFORMATION SCHEMA COLUMNS WHERE TABLE SCHEMA    table schema AND TABLE NAME    table name AND COLLATION NAME IS NOT NULL  -- Only strings have this and they always have it      OPEN columns cur      FETCH NEXT FROM columns cur INTO  column name     WHILE    FETCH STATUS   0      BEGIN         SET  sql string    IF EXISTS  SELECT   FROM     QUOTENAME  table schema          QUOTENAME  table name      WHERE     QUOTENAME  column name      LIKE         search string         PRINT       QUOTENAME  table schema          QUOTENAME  table name           QUOTENAME  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 schema   table name END  CLOSE tables cur  DEALLOCATE tables cur

User · Answer

The SSMS Tools PACK Add-In  Add-On  for Microsoft SQL Server Management Studio and Microsoft SQL Server Management Studio Express will do exactly what you need  On larger database it takes some time to search  but that is to be expected  It also includes a ton of cool features that should have be included with SQL Server Management Studio in the first place  Give it a try www ssmstoolspack com  You do need to have SP2 for SQL Server Management Studio installed to run the tools

User · Answer

This query can do the thing for you    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  99       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

User · Answer

This procedure is for finding any string or date in all tables if search string is date  its format should be yyyy-MM-dd eg  2011-07-05     --                                                  -- Exec SearchInTables  f6f56934-a5d4-4967-80a1-1a2223b9c7b1   --                                                  SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO --                                               -- Author        lt Joshy  Name gt  -- Create date   lt Create Date   gt  -- Description   lt Description   gt  --                                               ALTER PROCEDURE SearchInTables  myValue nvarchar 1000  AS BEGIN     -- SET NOCOUNT ON added to prevent extra result sets from     -- interfering with SELECT statements      SET NOCOUNT ON       -- Insert statements for procedure here         DECLARE  searchsql nvarchar max          DECLARE  table name nvarchar 1000          DECLARE  Schema name nvarchar 1000          DECLARE  ParmDefinition nvarchar 500          DECLARE  XMLIn nvarchar max          SET  ParmDefinition   N  XMLOut varchar max  OUTPUT           SELECT A name b name          FROM sys tables A              INNER JOIN sys schemas B ON A schema id B schema id         WHERE A name like  tbl Tax Sections            DECLARE tables cur CURSOR FOR                              SELECT A name b name FOM sys tables A                              INNER JOIN sys schemas B ON A schema id B schema id                             WHERE A type    U            OPEN tables cur           FETCH NEXT FROM tables cur INTO  table name    Schema name              WHILE    FETCH STATUS   0               BEGIN                 SET  searchsql   SELECT  XMLOut  SELECT PATINDEX        myValue                        SET  searchsql   searchsql        SELECT   FROM    Schema name      table name   FOR XML AUTO                      --print  searchsql                  EXEC sp executesql  searchsql   ParmDefinition   XMLOut  XMLIn OUTPUT                 --print  XMLIn                   IF  XMLIn  lt  gt  0 PRINT  Schema name      table name                  FETCH NEXT FROM tables cur INTO  table name    Schema name               END         CLOSE tables cur          DEALLOCATE tables cur      RETURN END GO

User · Answer

To  find where the data I get comes from   you can start SQL Profiler  start your report or application  and you will see all the queries issued against your database

User · Answer

Although the solutions presented before are valid and work  I humbly offer a code that s cleaner  more elegant  and with better performance  at least as I see it   Firstly  one may ask  Why would anyone ever need a code snippet to globally and blindly look for a string  Hey  they already invented fulltext  don t you know   My answer  my mainly work is at systems integration projects  and discovering where the data is written is important whenever I m learning a new and undocummented database  which seldom happens   Also  the code I present is a stripped down version of a more powerful and dangerous script that searches and REPLACES text throughout the database   CREATE TABLE  result    id      INT IDENTITY  -- just for register seek order   tblName VARCHAR 255     colName VARCHAR 255     qtRows  INT   go  DECLARE  toLookFor VARCHAR 255  SET  toLookFor     input your search criteria here    DECLARE cCursor CURSOR LOCAL FAST FORWARD FOR SELECT         usr name           tbl name       AS tblName          col name       AS colName    LOWER typ name  AS typName FROM   sysobjects tbl     INNER JOIN        syscolumns col         INNER JOIN systypes typ         ON typ xtype   col xtype           ON col id   tbl id     --     LEFT OUTER JOIN sysusers usr     ON usr uid   tbl uid  WHERE tbl xtype    U    AND LOWER typ name  IN           char    nchar            varchar    nvarchar            text    ntext          ORDER BY tbl name  col colorder -- DECLARE  tblName VARCHAR 255  DECLARE  colName VARCHAR 255  DECLARE  typName VARCHAR 255  -- DECLARE  sql  NVARCHAR 4000  DECLARE  crlf CHAR 2   SET  crlf   CHAR 13    CHAR 10   OPEN cCursor FETCH cCursor INTO  tblName   colName   typName  WHILE   fetch status   0 BEGIN   IF  typName IN  text    ntext     BEGIN     SET  sql          SET  sql    sql    INSERT INTO  result tblName  colName  qtRows      crlf     SET  sql    sql    SELECT  tblName   colName  COUNT        crlf     SET  sql    sql    FROM      tblName    crlf     SET  sql    sql    WHERE PATINDEX          toLookFor               colName       gt  0     crlf   END   ELSE   BEGIN     SET  sql          SET  sql    sql    INSERT INTO  result tblName  colName  qtRows      crlf     SET  sql    sql    SELECT  tblName   colName  COUNT        crlf     SET  sql    sql    FROM      tblName    crlf     SET  sql    sql    WHERE      colName     LIKE          toLookFor             crlf   END    EXECUTE sp executesql              sql              N  tblName varchar 255    colName varchar 255    toLookFor varchar 255                 tblName   colName   toLookFor    FETCH cCursor   INTO  tblName   colName   typName END  SELECT   FROM  result WHERE qtRows  gt  0 ORDER BY id GO  DROP TABLE  result go

User · Answer

Actually Im agree with MikeW   1  it s better to use profiler for this case   Anyway  if you really need to grab all  n varchar columns in db and make a search  See below  I suppose to use INFORMATION SCHEMA Tables   dynamic SQL  The plain search   DECLARE  SearchText VARCHAR 100   SET  SearchText    12  DECLARE  Tables TABLE N INT  TableName VARCHAR 100   ColumnNamesCSV VARCHAR 2000   SQL VARCHAR 4000    INSERT INTO  Tables  TableName  ColumnNamesCSV  SELECT  T TABLE NAME AS TableName             SELECT C Column Name                  FROM   INFORMATION SCHEMA Columns C            WHERE  T TABLE NAME   C TABLE NAME                   AND C DATA TYPE IN   nvarchar   varchar                     FOR XML PATH               FROM    INFORMATION SCHEMA Tables T   DELETE FROM  Tables WHERE ColumnNamesCSV IS NULL  INSERT INTO  Tables  N  TableName  ColumnNamesCSV  SELECT ROW NUMBER   OVER ORDER BY TableName   TableName  ColumnNamesCSV   FROM    Tables  DELETE FROM  Tables WHERE N IS NULL  UPDATE  Tables  SET ColumnNamesCSV   SUBSTRING ColumnNamesCSV  0  LEN ColumnNamesCSV    UPDATE  Tables  SET SQL    SELECT   FROM    TableName    WHERE      SearchText     IN    ColumnNamesCSV      DECLARE  C INT            I INT            SQL VARCHAR 4000   SELECT  I   1           C   COUNT 1   FROM    Tables  WHILE  I  lt    C BEGIN     SELECT  SQL   SQL FROM  Tables WHERE N    I     SET  I    I 1     EXEC  SQL  END   and one with LIKE clause   DECLARE  SearchText VARCHAR 100   SET  SearchText    12   DECLARE  Tables TABLE N INT  TableName VARCHAR 100   ColumnNamesCSVLike VARCHAR 2000   LIKESQL VARCHAR 4000    INSERT INTO  Tables  TableName  ColumnNamesCSVLike  SELECT   T TABLE NAME AS TableName                SELECT  C Column Name     LIKE       SearchText      OR                 FROM    INFORMATION SCHEMA Columns C               WHERE   T TABLE NAME   C TABLE NAME                       AND C DATA TYPE IN   nvarchar   varchar              FOR XML PATH      FROM     INFORMATION SCHEMA Tables T  DELETE FROM  Tables WHERE ColumnNamesCSVLike IS NULL  INSERT INTO  Tables  N  TableName  ColumnNamesCSVLike  SELECT ROW NUMBER   OVER ORDER BY TableName   TableName  ColumnNamesCSVLike  FROM  Tables  DELETE FROM  Tables WHERE N IS NULL  UPDATE  Tables  SET  ColumnNamesCSVLike   SUBSTRING ColumnNamesCSVLike  0  LEN ColumnNamesCSVLike -2   UPDATE  Tables SET LIKESQL    SELECT   FROM    TableName    WHERE   ColumnNamesCSVLike  DECLARE  C INT            I INT            LIKESQL VARCHAR 4000   SELECT  I   1           C   COUNT 1   FROM  Tables  WHILE  I  lt    C BEGIN     SELECT  LIKESQL   LIKESQL FROM  Tables WHERE N    I     SET  I    I  1     EXEC  LIKESQL  END

[sql] Search for a string in all tables, rows and columns of a DB

Examples related to sql

Examples related to sql-server-2005