PostgreSQL :- Datatype – bpchar

Background I went back and started reviewing the script uses for creating the customer table in the Northwind database. northwind pthom We are using the script furnished by pthom. It is published via GitHub and availed here. Here is the portion that creates the customer table What is bpchar? The customer_id column is defined as bpchar … Continue reading PostgreSQL :- Datatype – bpchar

PostgreSQL :- List Tables

Background Let us quickly list tables. Outline The Information Schema is an ANSI compliant Schema and works well across all database vendors. pg_catalog is PostgreSQL native. Here are the tables and views that we will use in this exercise. information_schema information_schema.tables pg_catalog pg_catalog.pg_tables pg_catalog.pg_class Query information_schema information_schema.tables SQL Output pg_catalog pg_catalog.pg_tables SQL Output pg_catalog.pg_class SQL … Continue reading PostgreSQL :- List Tables

DBeaver – Table Relationships

Background Just wanting to make sure that I am properly tracking table relationships in MySQL.   Code INFORMATION_SCHEMA INFORMATION_SCHEMA.KEY_COLUMN_USAGE Code Sample Output   Tools DBeaver Outline Steps to follow to track visually through DBeaver Launch DBeaver Connect to MySQL Instance Navigate to Database Tables <Specific Table> Double Click on the selected table On the right … Continue reading DBeaver – Table Relationships

MySQL – information_schema.statistics

Background As a quick follow-up to our discussion of metadata on tables, let us discuss indexes. BTW, the discussion on tables is here. INFORMATION_SCHEMA.STATISTICS Code Output   Comparison with Other Databases GROUP_CONCAT The group_concat is very, very powerful and has few equal in other database platforms Function Documentation   SHOW INDEX To get a bit … Continue reading MySQL – information_schema.statistics

Transact SQL – Constraint – Primary Key

There are a couple of approaches one can use to get the primary key for a table. Here are some of those ways: sp_pkeys sp_primarykeys INFORMATION_SCHEMA.TABLE_CONSTRAINTS sp_pkeys Syntax: Sample: Output: sp_primarykeys Though sp_primarykeys was added to gain insight into remote data sources, you can use it it to query your local data source, as well. … Continue reading Transact SQL – Constraint – Primary Key

Database – INFORMATION_SCHEMA – Use Case Scenario

Occasionally, one might find it useful to quickly get "structural" information about databases, tables, and columns. That is where the INFORMATION_SCHEMA object comes in.  They are part of ANSI Standards. Each DB Vendor calls them by different names: Microsoft calls them INFORMATION_SCHEMA IBM DB/2 and UDB calls them SYSIBM Oracle calls them Data Dictionary   … Continue reading Database – INFORMATION_SCHEMA – Use Case Scenario