No ERD?! No worries! Deriving the output code of a report table from the Information Schema

We’ve all been there. You turn up to work and your manager says you need to generate some reports from so-and-sos database.

Cool, no worries, we’ll just examine the ERD, write a couple of inner joins and Bob is your aunty’s live in lover.

“No worries boss, can you get the ERD sent over and I’ll get straight into it”.

“ERD? Who said anything about an ERD?”

This is going to be an interesting day, to say the least.

Luckily for you, you read crypticsql.com.

Problem Solution:

If we take a look at the information schema tables, look for a column that is an integer type – that is it say it is likely to be an id column, see if we can get a count > 1, so that the two results can join together, then we have the basis for generating a join statement.

SELECT TABLE_SCHEMA, COLUMN_NAME, COUNT(COLUMN_NAME) as CT, string_agg(TABLE_NAME,',') as [TABLES]

FROM INFORMATION_SCHEMA.COLUMNS

WHERE DATA_TYPE = 'int'

GROUP BY COLUMN_NAME, TABLE_SCHEMA

HAVING COUNT(COLUMN_NAME) > 1

Taking this query further we can generate the SELECT statements on the fly:

WITH X as (

SELECT TABLE_SCHEMA, COLUMN_NAME, COUNT(COLUMN_NAME) as CT, string_agg(TABLE_NAME,',') as [TABLES]

FROM INFORMATION_SCHEMA.COLUMNS

WHERE DATA_TYPE = 'int'

GROUP BY COLUMN_NAME, TABLE_SCHEMA

HAVING COUNT(COLUMN_NAME) > 1

)

SELECT *, 

CONCAT(CONCAT('SELECT a.*,b.* FROM Person.',LEFT([TABLES],CHARINDEX(',',[TABLES],1)-1),' as a INNER JOIN Person.'),

REVERSE(LEFT(REVERSE([TABLES]),CHARINDEX(',',REVERSE([TABLES]),1)-1)),' as b on a.',COLUMN_NAME,' = ','b.',COLUMN_NAME)

FROM X

WHERE CT = 2

AND TABLE_SCHEMA = 'Person'

Now we have our foot in the door, we can start building up the relevant queries and get some good insights for the client.

Maybe an early lunch is in order.

Oh!

Don't forget to pay the lady.