How to find a string inside a entire database

Question

I have one specific string  such as  123abcd  for example but I don t know the name of the table or even the name of the column inside the table on my SQL Server Database  I want to find it with a select and show all columns of the related string  so I was wondering something like   select   from Database dbo   where   like    123abcd     For obvious reasons it doens t work  but there is a simple way to create a select statement to do something like this

User · Answer

Here is an easy and convenient cursor based solution  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 name  object id FROM sys objects WHERE  type    U   OPEN tables cur  FETCH NEXT FROM tables cur INTO  table name   table id  WHILE    FETCH STATUS   0  BEGIN     DECLARE columns cur CURSOR FOR SELECT name FROM sys columns WHERE object id    table id          AND system type id IN  167  175  231  239       OPEN columns cur      FETCH NEXT FROM columns cur INTO  column name         WHILE    FETCH STATUS   0          BEGIN             SET  sql string    IF EXISTS  SELECT   FROM      table name     WHERE       column name                   LIKE         search string         PRINT        table name           column name                     EXECUTE  sql string           FETCH NEXT FROM columns cur INTO  column name         END      CLOSE columns cur  DEALLOCATE columns cur  FETCH NEXT FROM tables cur INTO  table name   table id END  CLOSE tables cur DEALLOCATE tables cur

User · Answer

Common Resource Grep  crgrep  will search for string matches in tables columns by name or content and supports a number of DBs  including SQLServer  Oracle and others  Full wild-carding and other useful options   It s opensource  I m the author     http   sourceforge net projects crgrep

User · Answer

SQL Locator  free  has worked great for me  It comes with a lot of options and it s fairly easy to use

User · Answer

Here are couple more free tools that can be used for this  Both work as SSMS addins   ApexSQL Search     100  free - searches both schema and data in tables  Has couple more useful options such as dependency tracking     SSMS Tools pack     free for all versions except SQL 2012     doesn   t look as advanced as previous one but has a lot of other cool features

User · Answer

I usually use information Schema columns and information schema tables  although like  yuck said  sys tables and sys columns are shorter to type   In a loop  concatenate these   sql    sql    select    column name      from     table name      where     column name   like       value      UNION   Then execute the resulting sql

User · Answer

create procedure usp find string  string as varchar 1000   as begin declare  mincounter as int declare  maxcounter as int declare  stmtquery as varchar 1000  set  stmtquery    create table  tmp tablename varchar 128  columnname varchar 128  rowid int identity  create table  tablelist tablename varchar 128  columnname varchar 128   declare  tmp table name varchar 128   declare  tablename as varchar 128  declare  columnname as varchar 128   insert into  tmp tablename columnname  select a name b name as columnname from sysobjects a inner join syscolumns b on a name object name b id  where a type  u  and b xtype in select xtype from systypes     where name  text  or name  ntext  or name  varchar  or name  nvarchar  or name  char  or name  nchar   order by a name  select  maxcounter max rowid   mincounter min rowid  from  tmp  while  mincounter  lt    maxcounter   begin  select  tablename tablename   columnname columnname from  tmp where rowid  mincounter  set  stmtquery   select top 1            columnname         from          tablename         where          columnname         like              string          insert into  tmp name  exec  stmtquery   if   rowcount  gt 0  insert into  tablelist values  tablename  columnname   set  mincounter  mincounter  1 end select   from  tablelist end

User · Answer

In oracle you can use the following sql command to generate the sql commands you need   select        select            from    table name          where    column name    like   123abcd      as sql command from user tab columns where data type  VARCHAR2

User · Answer

This will work   DECLARE  MyValue NVarChar 4000     something    SELECT S name SchemaName  T name TableName INTO  T FROM sys schemas S INNER JOIN      sys tables T ON S schema id   T schema id   WHILE  EXISTS  SELECT   FROM  T   BEGIN   DECLARE  SQL NVarChar 4000     SELECT   FROM   TableName WHERE  0   1       DECLARE  TableName NVarChar 1000          SELECT TOP 1 SchemaName         TableName FROM  T        SELECT  SQL   REPLACE  SQL     TableName    TableName      DECLARE  Cols NVarChar 4000           SELECT      Cols   COALESCE  Cols    OR CONVERT NVarChar 4000            C name        CONVERT NVarChar 4000       MyValue        FROM sys columns C   WHERE C object id   OBJECT ID  TableName      SELECT  Cols   REPLACE  Cols     MyValue    MyValue     SELECT  SQL    SQL    Cols     EXECUTE  SQL      DELETE FROM  T   WHERE SchemaName         TableName    TableName  END   DROP TABLE  T    A couple caveats  though  First  this is outrageously slow and non-optimized  All values are being converted to nvarchar simply so that they can be compared without error   You may run into problems with values like datetime not converting as expected and therefore not being matched when they should be  false negatives    The WHERE  0   1  is there to make building the OR clause easier  If there are not matches you won t get any rows back

User · Answer

I think you have to options    Build a dynamic SQL using sys tables and sys columns to perform the search  example here   Use any program that have this function  An example of this is SQL Workbench  free

[sql] How to find a string inside a entire database?

Examples related to sql

Examples related to database

Examples related to sql-server-2008

Examples related to select