mysql global search procedure

DROP PROCEDURE IF EXISTS findAll;

DELIMITER $$


CREATE PROCEDURE findAll(IN `search` TEXT)

BEGIN

    SET SESSION group_concat_max_len := @@max_allowed_packet;


    SELECT GROUP_CONCAT(

        "SELECT '", c1.TABLE_NAME, "' AS `table`, '", c1.COLUMN_NAME, "' AS `column`, ",

        "CONCAT_WS(',', ",  

            (SELECT GROUP_CONCAT(CONCAT('`', c2.column_name, '`')) 

             FROM `information_schema`.`columns` c2 

             WHERE c1.TABLE_SCHEMA=c2.TABLE_SCHEMA 

               AND c1.TABLE_NAME=c2.TABLE_NAME 

               AND c2.COLUMN_KEY='PRI'), 

        ") AS pri, ",

        "CONVERT(`", c1.COLUMN_NAME, "` USING utf8mb4) COLLATE utf8mb4_general_ci AS value FROM `", c1.TABLE_NAME,

        "` WHERE CONVERT(`", c1.COLUMN_NAME, "` USING utf8mb4) COLLATE utf8mb4_general_ci LIKE '%", search, "%'"

        SEPARATOR "\nUNION\n"

    ) INTO @sql

    FROM information_schema.columns c1 

    WHERE c1.TABLE_SCHEMA = DATABASE()

      AND c1.DATA_TYPE IN ('char', 'varchar', 'text', 'tinytext', 'mediumtext', 'longtext');


    -- End the query properly

    SET @sql = CONCAT(@sql, ";");


    -- Debug (optional): SELECT @sql;


    PREPARE stmt FROM @sql;

    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;

END $$


DELIMITER ;


Comments

Post a Comment

Popular posts from this blog

Grouping Sets, With Rollup and With Cube example

Image
Grouping sets allow multiple grouping on columns in a single data set. Rollup and Cube also used for summary rows (allows multiple grouping same as Grouping sets) Example: DECLARE @t TABLE     (       idx INT IDENTITY(1, 1) ,       Country VARCHAR(50) ,       City VARCHAR(50) ,       NoOfJobOpenings INT     ); INSERT  INTO @t         ( Country ,           City ,           NoOfJobOpenings         )         SELECT  'Pakistain' ,                 'Karachi' ,                 50         UNION         SELECT  'Pakistain' ,                 'Lahore' ,             ...
Read more

Adding Page brake aganist group values

If you want to change pages when a group value changes(adding page breaks on group values ). then select group from bottom and right click on it and go  to group properties. select page break option from the left options. and then checked the box named"between each instance of a group"....this will add page break againt changing of group value....set its child properties according to your need.
Read more

To Get All the Names of tables and Databases their data have changed in last 24 Hours And Backup those databases

-- ============================================= -- Author: Muhammad Jawad -- Create date: 2/3/2017 -- Description: To Get All the Names of tables and Databases their data have changed in last 24 Hours And Backup those databases -- ============================================= Alter PROC UP_FindAndBackupAllDatabaseHaveChanges AS     BEGIN ----**************************START: To get all the tables and database names whose data have changed in last 24 hours **************************----         DECLARE @loopStart INT= 1;         DECLARE @looend INT;         IF OBJECT_ID('tempdb..#AllDatabaseTables') IS NOT NULL             BEGIN                 DROP TABLE #AllDatabaseTables;             END;         CREATE TABLE #AllDatabaseTables             ( ...
Read more