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

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    = 
  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

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

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

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
© 2024 Joel Lipman .com. All Rights Reserved.