How do I verify the database schema in Pipeline Cloud?

Overview 

Before querying your database, it's helpful to understand its structure. Pipeline Cloud provides Information Schema views that allow you to explore tables, columns, and relationships in your database. 

Step 1: View all tables in your database 

1. Run the following query to see all tables: 

SELECT TABLE_NAME, TABLE_TYPE, TABLE_SCHEMA  

FROM INFORMATION_SCHEMA.TABLES 

2. This will display a list of all tables in your database along with their types and schemas 

Step 2: Explore column information 

1. Run the following query to see all column details: 

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT  

FROM INFORMATION_SCHEMA.COLUMNS 

2. To filter for a specific table, add a WHERE clause: 

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH  

FROM INFORMATION_SCHEMA.COLUMNS 

WHERE TABLE_NAME = 'Contacts' 

Step 3: Identify key columns and constraints 

1. Run the following query to view key column information: 

SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, CONSTRAINT_SCHEMA, TABLE_SCHEMA   

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 

2. This will help you identify primary keys and relationships between tables 

What else do you need help with? 

• Understanding Pipeline Cloud 
• How do I adapt MySQL queries to Microsoft SQL? 
• How do I connect Pipeline Cloud to Power BI?