Tags

Other

Your database doesn't just contain your data.

It also contains data about your data.



SQL Server

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.

Examples of System Views

  • sys.objects - shows each object, its type and created date
  • sys.indexes - shows each index and type
  • information_schema.columns - shows each column, it's position and datatype


PostgreSQL

In PostgreSQL a similar collection of tables can be found in the information_schema and PostgreSQL catalog.

Examples of Catalog Objects

  • information_schema.tables - shows each object, its type and created date
  • pg_index - shows each index and type
  • information_schema.columns - shows each column, it's position and datatype


Useful scripts

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)



 

Count Columns

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;



 

Count Rows

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;



 

Show data types

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';




 

Search for a column name

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



 

Show all tables in a schema

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



 

Show number of tables in each schema

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


Guest Blogger


Special thanks to our guest blogger Helen Anderson, a BI Consultant for her contribution to the Ronald James Blog this week.

twitter helenanders26  github helenanders26 

Who Are Ronald James?

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.

Contact our Team

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.

Let's be Friends!

Follow us on our blog, FacebookLinkedInTwitter or Instagram to follow industry news, events, success stories and new blogs releases.

 

 

Back to Blog

</Follow Us>