SJGP Software

  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 (   ...

The Problem We want to calculate the size of each schema on our database server. We want to do this through SQL, not with a disk-based process. Sample use-case: an established system gives separate schemas to each client. Now it wants to begin charging a small fee based on the amount of space used by each client. MySQL Schema Sizes We can gather this data using the information_schema in MySQL. The "tables" table holds data and index size values for the tables of each schema, so we can group and sum them. We'll also throw in some formatting of the size, alongside the raw large integer of the total size. I've also dropped the ORDER BY clause found in the screenshot, as for my use case above I am not concerned with that ordering of the output. SELECT table_schema 'Database Name',   SUM(data_length + index_length) 'Size in Bytes',   sys.FORMAT_BYTES(SUM(data_length + index_length)) 'Size (Formatted)' FROM information_schema.tables  GROUP BY table_s...

  Find All Foreign Key References In my experience, a well-Normalized database winds up with lots of Foreign Key relationships between the tables. They are a real benefit, bringing so much ease and power in preserving data integrity. Best of all, they pass responsibility for maintaining that integrity to the RDBMS itself, rather than burying it in application code. Looking at one table, it is relatively easy to see its list of Foreign Keys outward, to other tables. Occasionally, though, we need to know what other tables have a Foreign Key defined for a given table. It is rare enough that I usually forget some of the little details in between uses. Rather than needing to do a web search every time, this Tickler post will help to jog my memory. MySQL -- what FKs reference this data? SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'MySchema' AND REFERENCED_TABLE_...