Your database doesn't just contain your data.
It also contains data about your data.
In SQL Server these are often referred to as system tables and views. They can be found in the master database, which holds data about the database. And in the system views within each database for specific information about each database.
In PostgreSQL a similar collection of tables can be found in the information_schema and PostgreSQL catalog.
To illustrate how useful these can be, and which views and tables you need, here are six scripts in SQL Server and PostgreSQL.
Count columns
Count rows
Show data types
Search for a column name
Show all tables in a schema
Show number of tables in each schema
Tested using SQL Server Standard and AWS Aurora (Postgres)
This query returns a list of tables, in alphabetical order, with a count of the columns. Add your schema or database name to the code and run the query.
-- SQL Server
select
table_schema,
table_name,
count(*) as column_count
from information_schema.columns
where table_catalog = 'mydatabase' -- put your DB here
group by table_schema, table_name
order by table_schema, table_name
-- PostgreSQL
select table_name, count(column_name)
from information_schema.columns
where table_schema = 'myschema' -- put your schema here
group by table_name
order by table_name;
This query returns a list of tables, in alphabetical order, with a count of the rows. In the case of SQL Server, this column will contain the schema and table name. Add your schema or database name to the code and run the query.
-- SQL Server
use mydatabase -- put your DB here
go
select
quotename(schema_name(sobj.schema_id)) + '.' + quotename(sobj.name) as table_name,
sum(sptn.rows) as row_count
from
sys.objects as sobj
inner join sys.partitions as sptn
on sobj.object_id = sptn.object_id
where
sobj.type = 'U'
and sobj.is_ms_shipped = 0x0
and index_id < 2 -- 0:Heap, 1:Clustered
group by
sobj.schema_id,
sobj.name
order by table_name
-- PostgreSQL
select
schemaname,
relname,
n_live_tup
from pg_stat_user_tables
where schemaname = 'myschema'
order by relname;
This query returns a list of tables, in alphabetical order, with their column names, data types and lengths. In the case of SQL Server, this also has a column for the schema name. Add your schema or database name to the code and run the query.
-- SQL Server
use mydatabase -- put your DB here
go
select
c.table_schema,
c.table_name,
c.column_name,
c.data_type,
c.character_maximum_length
from information_schema.columns c
join sys.objects o
on c.table_name = o.name
where type = 'u' -- u is for user created tables
order by c.table_schema
-- PostgreSQL
select
table_schema,
table_name,
column_name,
data_type
from information_schema.columns
where table_schema = 'myschema';
This query returns a list of column names that match the search criteria in the WHERE clause. Add your schema or database name to the code and run the query.
-- SQL Server
use mydatabase -- put your DB here
go
select
scol.name as column_name,
tab.name as table_name
from
sys.columns scol
join sys.tables tab
on scol.object_id = tab.object_id
where scol.name = 'mycolumn' --put your column name here
-- PostgreSQL
select
column_name,
table_name
from information_schema.columns
where column_name = 'mycolumn' --put your column name here
and table_schema = 'myschema' -- put your schema here
This query returns a list of tables, in alphabetical order, from the schema or database requested. Add your schema or database name to the code and run the query.
-- SQL Server
use mydatabase -- put your DB here
go
select [table] = s.name + N'.' + t.name
from sys.tables AS t
inner join sys.schemas AS s
on t.schema_id = s.schema_id
order by [table]
-- PostgreSQL
select
table_name
from information_schema.tables
where table_schema = 'myschema' -- put your schema here
order by table_name
This query returns a list of tables, in alphabetical order, with their last modified and created date. Add your schema or database name to the code and run the query.
-- SQL Server
use mydatabase -- put your DB here
go
select schema_name(schema_id) as schema_name,
count(name) as table_count
from sys.tables
group by schema_name(schema_id)
order by schema_name(schema_id)
-- PostgreSQL
select schemaname, count(tablename)
from pg_tables
group by schemaname
order by schemaname
This post first appeared on helenanderson.co.nz
Picture by Pixabay On Pexels
Special thanks to our guest blogger Helen Anderson, a BI Consultant for her contribution to the Ronald James Blog this week.
We are a leading niche digital & tech recruitment specialist for the North East of England. We Specialise in the acquisition of high-performing technology talent across a variety of IT sectors including Digital & Technology Software Development.
Our ultimate goal is to make a positive impact on every client and candidate we serve - from the initial call and introduction, right up to the final delivery, we want our clients and candidates to feel they have had a beneficial and productive experience.
If you’re looking to start your journey in sourcing talent or find your dream job, you’ll need a passionate, motivated team of experts to guide you. Check out our Jobs page for open vacancies. If interested, contact us or call 0191 620 0123 for a quick chat with our team.
Follow us on our blog, Facebook, LinkedIn, Twitter or Instagram to follow industry news, events, success stories and new blogs releases.
Back to Blog