Search a database for a string (MySQL, T-SQL)

Category: Databases
Hits: 40239
What?
I have a PHP script which behaves like PhpMyAdmin, in that it automatically lists a database structure and the fields to modify similar to an excel spreadsheet. Now without using PHP, I need standalone SQL scripts that can simply be used to search an entire database for a particular string of word(s).

I've posted my own as well as some others I've lifted from elsewhere, as they worked with my environment, to put them in one place on a website I've bookmarked (my personal site :c)


My own with Typos
Note that the below only prints out the SQL query to copy and paste into your SQL administration software. You can put a distinct on it (not forgetting the collate). This example is expanded in a further article called "Search a database with soundex":
copyraw 
/* USING A CURSOR */
DECLARE @SqlToExecute nvarchar(max);
DECLARE @mySearchString varchar(50);
SET @mySearchString = 'dnya';
DECLARE MyCursor CURSOR FOR
		SELECT
			'SELECT ' + COLUMN_NAME + ' COLLATE Latin1_General_CS_AS AS column1 FROM ' + (TABLE_SCHEMA + '.' + TABLE_NAME) + ' WHERE SOUNDEX(' + COLUMN_NAME + ')=SOUNDEX('''+ @mySearchString +''')' + CHAR(10)
		FROM
			INFORMATION_SCHEMA.COLUMNS
		WHERE
			DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext')
			AND TABLE_SCHEMA='dbo'
		ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;

OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @SqlToExecute
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT ' union all ' + @SqlToExecute;
    FETCH NEXT FROM MyCursor INTO @SqlToExecute
END
CLOSE MyCursor
DEALLOCATE MyCursor
  1.  /* USING A CURSOR */ 
  2.  DECLARE @SqlToExecute nvarchar(max); 
  3.  DECLARE @mySearchString varchar(50); 
  4.  SET @mySearchString = 'dnya'; 
  5.  DECLARE MyCursor CURSOR FOR 
  6.          SELECT 
  7.              'SELECT ' + COLUMN_NAME + ' COLLATE Latin1_General_CS_AS AS column1 FROM ' + (TABLE_SCHEMA + '.' + TABLE_NAME) + ' WHERE SOUNDEX(' + COLUMN_NAME + ')=SOUNDEX('''+ @mySearchString +''')' + CHAR(10) 
  8.          FROM 
  9.              INFORMATION_SCHEMA.COLUMNS 
  10.          WHERE 
  11.              DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext') 
  12.              AND TABLE_SCHEMA='dbo' 
  13.          ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME; 
  14.   
  15.  OPEN MyCursor 
  16.  FETCH NEXT FROM MyCursor INTO @SqlToExecute 
  17.  WHILE @@FETCH_STATUS = 0 
  18.  BEGIN 
  19.      PRINT ' union all ' + @SqlToExecute; 
  20.      FETCH NEXT FROM MyCursor INTO @SqlToExecute 
  21.  END 
  22.  CLOSE MyCursor 
  23.  DEALLOCATE MyCursor 


T-SQL
Source: http://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58 - Sorna Kumar
copyraw 
IF OBJECT_ID('usp_SearchDB','P') IS NOT NULL 
    DROP PROCEDURE usp_SearchDB
GO 
CREATE PROCEDURE usp_SearchDB
 @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 & 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  '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
  1.  IF OBJECT_ID('usp_SearchDB','P') IS NOT NULL 
  2.      DROP PROCEDURE usp_SearchDB 
  3.  GO 
  4.  CREATE PROCEDURE usp_SearchDB 
  5.   @Tablenames VARCHAR(500) 
  6.  ,@SearchStr NVARCHAR(60) 
  7.  ,@GenerateSQLOnly Bit = 0 
  8.  AS 
  9.   
  10.  /* 
  11.      Parameters and usage 
  12.   
  13.      @Tablenames        -- Provide a single table name or multiple table name with comma seperated. 
  14.                          If left blank , it will check for all the tables in the database 
  15.      @SearchStr        -- Provide the search string. Use the '%' to coin the search. 
  16.                          EX : X%--- will give data staring with X 
  17.                               %X--- will give data ending with X 
  18.                               %X%--- will give data containig  X 
  19.      @GenerateSQLOnly -- Provide 1 if you only want to generate the SQL statements without seraching the database. 
  20.                          By default it is 0 and it will search. 
  21.   
  22.      Samples : 
  23.   
  24.      1. To search data in a table 
  25.   
  26.          EXEC SearchTables @Tablenames = 'T1' 
  27.                           ,@SearchStr  = '%TEST%' 
  28.   
  29.          The above sample searches in table T1 with string containing TEST. 
  30.   
  31.      2. To search in a multiple table 
  32.   
  33.          EXEC SearchTables @Tablenames = 'T2' 
  34.                           ,@SearchStr  = '%TEST%' 
  35.   
  36.          The above sample searches in tables T1 & T2 with string containing TEST. 
  37.   
  38.      3. To search in a all table 
  39.   
  40.          EXEC SearchTables @Tablenames = '%' 
  41.                           ,@SearchStr  = '%TEST%' 
  42.   
  43.          The above sample searches in all table with string containing TEST. 
  44.   
  45.      4. Generate the SQL for the Select statements 
  46.   
  47.          EXEC SearchTables @Tablenames        = 'T1' 
  48.                           ,@SearchStr        = '%TEST%' 
  49.                           ,@GenerateSQLOnly    = 1 
  50.   
  51.  */ 
  52.   
  53.      SET NOCOUNT ON 
  54.   
  55.      DECLARE @CheckTableNames Table 
  56.      ( 
  57.      Tablename sysname 
  58.      ) 
  59.   
  60.      DECLARE @SQLTbl TABLE 
  61.      ( 
  62.       Tablename        SYSNAME 
  63.      ,WHEREClause    VARCHAR(MAX) 
  64.      ,SQLStatement   VARCHAR(MAX) 
  65.      ,Execstatus        BIT 
  66.      ) 
  67.   
  68.      DECLARE @sql VARCHAR(MAX) 
  69.      DECLARE @tmpTblname sysname 
  70.   
  71.      IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%') 
  72.      BEGIN 
  73.   
  74.          INSERT INTO @CheckTableNames 
  75.          SELECT Name 
  76.            FROM sys.tables 
  77.      END 
  78.      ELSE 
  79.      BEGIN 
  80.   
  81.          SELECT @sql = 'SELECT ''' + REPLACE(@Tablenames,',',''' UNION SELECT ''') + '''' 
  82.   
  83.          INSERT INTO @CheckTableNames 
  84.          EXEC(@sql) 
  85.   
  86.      END 
  87.   
  88.      INSERT INTO @SQLTbl 
  89.      ( Tablename,WHEREClause) 
  90.      SELECT SCh.name + '.' + ST.NAME, 
  91.              ( 
  92.                  SELECT '[' + SC.name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10) 
  93.                    FROM SYS.columns SC 
  94.                    JOIN SYS.types STy 
  95.                      ON STy.system_type_id = SC.system_type_id 
  96.                     AND STy.user_type_id =SC.user_type_id 
  97.                   WHERE STY.name in ('varchar','char','nvarchar','nchar') 
  98.                     AND SC.object_id = ST.object_id 
  99.                   ORDER BY SC.name 
  100.                  FOR XML PATH('') 
  101.              ) 
  102.        FROM  SYS.tables ST 
  103.        JOIN @CheckTableNames chktbls 
  104.                  ON chktbls.Tablename = ST.name 
  105.        JOIN SYS.schemas SCh 
  106.          ON ST.schema_id = SCh.schema_id 
  107.       WHERE ST.name  'SearchTMP' 
  108.        GROUP BY ST.object_id, SCh.name + '.' + ST.NAME ; 
  109.   
  110.        UPDATE @SQLTbl 
  111.           SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5) 
  112.   
  113.        DELETE FROM @SQLTbl 
  114.         WHERE WHEREClause IS NULL 
  115.   
  116.      WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0) 
  117.      BEGIN 
  118.   
  119.          SELECT TOP 1 @tmpTblname = Tablename , @sql = SQLStatement 
  120.            FROM @SQLTbl 
  121.           WHERE ISNULL(Execstatus ,0) = 0 
  122.   
  123.   
  124.   
  125.           IF @GenerateSQLOnly = 0 
  126.           BEGIN 
  127.   
  128.              IF OBJECT_ID('SearchTMP','U') IS NOT NULL 
  129.                  DROP TABLE SearchTMP 
  130.              EXEC (@SQL) 
  131.   
  132.              IF EXISTS(SELECT 1 FROM SearchTMP) 
  133.              BEGIN 
  134.                  SELECT Tablename=@tmpTblname,* FROM SearchTMP 
  135.              END 
  136.   
  137.           END 
  138.           ELSE 
  139.           BEGIN 
  140.               PRINT REPLICATE('-',100) 
  141.               PRINT @tmpTblname 
  142.               PRINT REPLICATE('-',100) 
  143.               PRINT replace(@sql,'INTO SearchTMP','') 
  144.           END 
  145.   
  146.           UPDATE @SQLTbl 
  147.              SET Execstatus = 1 
  148.            WHERE Tablename = @tmpTblname 
  149.   
  150.      END 
  151.   
  152.      SET NOCOUNT OFF 
  153.   
  154.  GO 


MySQL
Source: http://kedar.nitty-witty.com/blog/search-through-all-databases-tables-columns-in-mysql - Kedar at Nitty-Witty.com 
## Procedure for search in all fields of all databases
DELIMITER $$
#Script to loop through all tables using Information_Schema
DROP PROCEDURE IF EXISTS get_table $$
CREATE PROCEDURE get_table(in_search varchar(50))
READS SQL DATA
BEGIN
DECLARE trunc_cmd VARCHAR(50);
DECLARE search_string VARCHAR(250);
DECLARE db,tbl,clmn CHAR(50);
DECLARE done INT DEFAULT 0;
DECLARE COUNTER INT;
DECLARE table_cur CURSOR FOR
SELECT concat('SELECT COUNT(*) INTO @CNT_VALUE FROM `',table_schema,'`.`',table_name,'` WHERE `', column_name,'` REGEXP "',in_search,'"') ,table_schema,table_name,column_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA IN ('network_detail');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
#Truncating table for refill the data for new search.
PREPARE trunc_cmd FROM "TRUNCATE TABLE temp_details;";
EXECUTE trunc_cmd ;
OPEN table_cur;
table_loop:LOOP
FETCH table_cur INTO search_string,db,tbl,clmn;
#Executing the search
SET @search_string = search_string;
#SELECT search_string;
PREPARE search_string FROM @search_string;
EXECUTE search_string;
SET COUNTER = @CNT_VALUE;
#SELECT COUNTER;
IF COUNTER>0 THEN
# Inserting required results from search to table
INSERT INTO temp_details VALUES(db,tbl,clmn);
END IF;
IF done=1 THEN
LEAVE table_loop;
END IF;
END LOOP;
CLOSE table_cur;
#Finally Show Results
SELECT concat("SELECT * FROM ",t_table, " WHERE ", t_field, " REGEXP '", in_search, "';") FROM temp_details;
END $$
DELIMITER ;
Category: Databases :: Article: 437

Add comment

Your rating:

Submit

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Latest Articles

Accreditation

Badge - Zoho Creator Certified Developer Associate
Badge - Zoho Deluge Certified Developer
Badge - Certified Zoho CRM Developer

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF

Joes Word Cloud

Please publish modules in offcanvas position.