Search for one value in any column of any table inside a database

Question

Is there a way to search for one value  in my case it is a UID of the type char 64   inside any column of any table inside one MS SQL Server database   I m sitting in front of a huge database without any idea how the tables had to be linked together  To find that out I d like to list all tables and there columns that contain a certain value in any row  Is that possible   One way could be to just dump the entire database into a text file and than use any text-editor to search for the value - but this would be pure pain if the database is too huge

User · Answer

There is a nice script available on http://www.reddyss.com/SQLDownloads.aspx

To be able to use it on any database you can create it like in: http://nickstips.wordpress.com/2010/10/18/sql-making-a-stored-procedure-available-to-all-databases/

Not sure if there is other way.

To use it then use something like this:

use name_of_database

EXEC spUtil_SearchText 'value_searched', 0, 0

User · Answer

Source  http   fullparam wordpress com 2012 09 07 fck-it-i-am-going-to-search-all-tables-all-collumns   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

User · Answer

How to search all columns of all   tables in a database for a keyword    http   vyaskn tripod com search all columns in all tables htm  EDIT  Here s the actual T-SQL  in case of link rot    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 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 found a fairly robust solution at https   gallery technet microsoft com scriptcenter c0c57332-8624-48c0-b4c3-5b31fe641c58   which I thought was worth pointing out   It searches columns of these types  varchar  char  nvarchar  nchar  text   It works great and supports specific table-searching as well as multiple search-terms

User · Answer

After trying  regeter s solution and seeing it didn t solve my problem when I was searching for a foreign primary key to see all tables columns where it exists  it didn t work   After reading how it failed for another who tried to use a unique identifier  I have made the modifications and here is the updated result   works with both int  and guids    you will see how to easily extend   CREATE PROC  dbo   SearchAllTables Like             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 DATA TYPE IN   guid    int    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

All third party tools mentioned below are 100  free    I   ve used ApexSQL Search with good success for searching both objects and data in tables  It comes with several other features such as relationship diagrams and such      I was a bit slow on large  40GB TFS Database  databases though        Apart from this there is also SSMS Tools pack that offers a lot of other features that are quite useful even though these are not directly related to searching text

User · Answer

I expanded the code  because it s not told me the  record number   and I must to refind it   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  -- Copyright   2012 Gyula Kulifai  All rights reserved  -- Extended By  Gyula Kulifai -- Purpose  To put key values  to exactly determine the position of search -- Resources  Anatoly Lubarsky -- Date extension  19th October 2012 12 24 GMT -- Tested on  SQL Server 10 0 5500  SQL Server 2008 SP3   CREATE TABLE  Results  TableName nvarchar 370   KeyValues nvarchar 3630   ColumnName nvarchar 370   ColumnValue nvarchar 3630    SET NOCOUNT ON  DECLARE  TableName nvarchar 256    ColumnName nvarchar 128    SearchStr2 nvarchar 110        TableShortName nvarchar 256        TableKeys nvarchar 512        SQL nvarchar 3830   SET   TableName      SET  SearchStr2   QUOTENAME        SearchStr              WHILE  TableName IS NOT NULL BEGIN     SET  ColumnName           -- Scan Tables     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           Set  TableShortName PARSENAME  TableName  1      -- print  TableName          TableShortName      --     DEBUG LINE              -- LOOK Key Fields  Set Key Columns         SET  TableKeys            SELECT  TableKeys    TableKeys          QUOTENAME  name             CONVERT nvarchar 250       name                                      FROM syscolumns           WHERE  id  IN               SELECT  id                FROM sysobjects               WHERE  name     TableShortName             AND colid IN               SELECT SIK colid               FROM sysindexkeys SIK               JOIN sysobjects SO ON                  SIK  id    SO  id                 WHERE                  SIK indid   1                 AND SO  name     TableShortName          If  TableKeys lt  gt                SET  TableKeys SUBSTRING  TableKeys 1 Len  TableKeys -8          -- Print  TableName          TableKeys       --     DEBUG LINE          -- Search in Columns     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           -- Set ColumnName          IF  ColumnName IS NOT NULL         BEGIN             SET  SQL                   SELECT                             TableName                               TableKeys                               ColumnName                        LEFT      ColumnName      3630                   FROM      TableName      NOLOCK                        WHERE      ColumnName     LIKE      SearchStr2             --Print  SQL --     DEBUG LINE                 INSERT INTO  Results                 Exec   SQL          END -- IF ColumnName     END -- While Table and Column END --While Table  SELECT TableName  KeyValues  ColumnName  ColumnValue FROM  Results END

[tsql] Search for one value in any column of any table inside a database

Examples related to tsql