Search all tables all columns for a specific value SQL Server

Question

I have a specific value  let s say string  comments    I need to find all instances of this in the database as I need to do an update on the format to change it to     Comments   How can I do this  The database is in SQL Server 2000 format

User · Answer

You could query the sys tables database view to get out the names of the tables  and then use this query to build yourself another query to do the update on the back of that   For instance   select  select   from   name from sys tables   will give you a script that will run a select   against all the tables in the system catalog  you could alter the string in the select clause to do your update  as long as you know the column name is the same on all the tables you wish to update  so your script would look something like   select  update   name   set comments           comments where comments like    comment to be updated      from sys tables   You could also then predicate on the tables query to only include tables that have a name in a certain format  or are in a subset you want to create the update script for

User · Answer

The below Query works but very slow    copied from vyaskn tripod com  Declare  SearchStr nvarchar 100   SET   SearchStr  Search String  BEGIN  CREATE TABLE  Results  ColumnName nvarchar 370   ColumnValue nvarchar 3630    SET NOCOUNT ON  DECLARE  TableName nvarchar 256    ColumnName nvarchar 128     SearchStr2 nvarchar 110   SET   TableName         SET  SearchStr2    QUOTENAME        SearchStr              WHILE  TableName IS NOT NULL     BEGIN          SET  ColumnName              SET  TableName          SELECT MIN QUOTENAME TABLE SCHEMA              QUOTENAME TABLE NAME   FROM INFORMATION SCHEMA TABLES      WHERE     TABLE TYPE    BASE TABLE      AND QUOTENAME TABLE SCHEMA          QUOTENAME TABLE NAME   gt   TableName     AND OBJECTPROPERTY        OBJECT ID QUOTENAME TABLE SCHEMA          QUOTENAME TABLE NAME             IsMSShipped     0     WHILE   TableName IS NOT NULL  AND   ColumnName IS NOT NULL          BEGIN     SET  ColumnName           SELECT MIN QUOTENAME COLUMN NAME         FROM INFORMATION SCHEMA COLUMNS       WHERE TABLE SCHEMA   PARSENAME  TableName  2          AND TABLE NAME   PARSENAME  TableName  1        AND DATA TYPE IN   char    varchar    nchar    nvarchar         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

I published one here  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

I ve just updated my blog post to correct the error in the script that you were having Jeff  you can see the updated script here  Search all fields in SQL Server Database  As requested  here s the script in case you want it but I d recommend reviewing the blog post as I do update it from time to time  DECLARE  SearchStr nvarchar 100  SET  SearchStr       YOUR STRING HERE           -- 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 -- Updated and tested by Tim Gaunt -- http   www thesitedoctor co uk -- http   blogs thesitedoctor co uk tim 2010 02 19 Search Every Table And Field In A SQL Server Database Updated aspx -- Tested on  SQL Server 7 0  SQL Server 2000  SQL Server 2005 and SQL Server 2010 -- Date modified  03rd March 2011 19 00 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       DATA TYPE IN   char    varchar    nchar    nvarchar    int    decimal                   AND       QUOTENAME COLUMN NAME   gt   ColumnName                      IF  ColumnName IS NOT NULL                    BEGIN             INSERT INTO  Results             EXEC                                SELECT        TableName          ColumnName        LEFT      ColumnName      3630  FROM      TableName      NOLOCK                        WHERE      ColumnName     LIKE      SearchStr2                       END     END       END    SELECT ColumnName  ColumnValue FROM  Results    DROP TABLE  Results

[sql-server] Search all tables, all columns for a specific value SQL Server

Examples related to sql-server