MySQL Report Across Schemas (Tickler)

 

The Problem

We want to gather information about the same data entities, scattered across diverse schemas.

How to apply the same SELECT query in each schema and gather the results into a single output?

A sample Use Case: we want a list of user login names across all schemas, so we can analyze possible conflicts in moving to a centralized identity management system.

MySQL Cross-Schema Reporting

Screenshot of results of this process, from MySQL Workbench:

Our approach is to create a stored procedure that will walk through all schemas on the server, run the same query to gather the data, and put the collected data into a dedicated table in an administrative schema for future reporting and analysis.

First, let's create a new schema for this Reporting exercise, and select it as the schema in use.

CREATE DATABASE CustomReportingSPS;

USE CustomReportingSPS;

Next, create a table that will collect the results of the query as it is run across schemas.

CREATE TABLE rpt_active_users (

  DB_NAME varchar(200),

    ORG_NAME varchar(200),

    USER_LOGIN_NAME varchar(20),

    NAME_FIRST varchar(100),

    NAME_LAST varchar(200),

    ACTIVE varchar(20)  );

Next, build a stored procedure that will iterate over the schemas.

DELIMITER $$

DROP PROCEDURE IF EXISTS `query_all_schemas`$$

CREATE PROCEDURE `query_all_schemas`()

BEGIN

    -- declare variable for database name

    DECLARE dbname VARCHAR(128) DEFAULT '';

    DECLARE done INTEGER DEFAULT 0;

    -- declare NOT FOUND handler

    DECLARE CONTINUE HANDLER 

    FOR NOT FOUND SET done = 1;

The Cursor we define here allows us to omit tables that we want to skip running the query in. These include any system schemas, this reporting schema we just created, and any test schemas.

    DECLARE schema_list CURSOR FOR 

      SELECT DISTINCT SCHEMA_NAME AS `database`

      FROM `information_schema`.SCHEMATA

      WHERE SCHEMA_NAME NOT IN ( 'information_schema', 'performance_schema', 'mysql', 'sys', 'world','CustomReportingSPS' )

      ORDER BY SCHEMA_NAME;

Now let's iterate through the Cursor to get each schema in turn:

    OPEN schema_list;

    query_table: LOOP

        FETCH schema_list INTO dbname;

        IF done = 1 THEN

        LEAVE query_table;

        END IF;

Once we have the schema name in the dbname variable, create the SQL statement with concatenation. We need to select from the schema and write the data into the reporting schema created above.

        SET @s = CONCAT(

'INSERT INTO CustomReportingSPS.rpt_active_users '

'SELECT "', dbname, '", o.ORG_NAME, u.USER_LOGIN_NAME, p.NAME_FIRST, p.NAME_LAST, u.IS_ACTIVE

FROM ', dbname, '.Users u

LEFT OUTER JOIN ', dbname, '.Persons p ON p.ID = u.PERSON_ID

JOIN ', dbname, '.Organizations o'

);

With the SQL query ready let's Prepare and Execute it, then loop back to get the next schema.

        PREPARE stmt FROM @s;

        EXECUTE stmt;

        DEALLOCATE PREPARE stmt; 

    END LOOP    query_table;

    CLOSE schema_list;

And close our Stored Procedure definition

END$$

DELIMITER ;

To run this stored procedure:

CALL query_all_schemas();

These "memory tickler" posts are quick-hits to refresh my mind about little technical details that arise infrequently enough that I forget in between, and wind up doing an internet search every time. By posting them here, they will be easier to find as needed.)