loading...
Cover image for How to search with your database System Tables

How to search with your database System Tables

helenanders26 profile image Helen Anderson Updated on ・4 min read

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. 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 catalog objects.

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 for 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


Read more


This post first appeared on helenanderson.co.nz

Posted on by:

helenanders26 profile

Helen Anderson

@helenanders26

Making applications go faster at Raygun, AWS Data Hero, and tag moderator on Dev.to.

Discussion

pic
Editor guide
 

Nice article, thanks for that.

Maybe you want to add this one:

gist.github.com/m0veax/d5ecb066475...

It compares the schemas itself between to databases in SQL Server and is really useful to trace down differences between Dev instances.

But I don't know about a PostgreSQL counterpart