Search with system tables | Helen Anderson

Your database doesn’t just contain your data.

It also contains data about your data in system tables.

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, type and created date

sys.indexes – shows each index and type

information_schema.columns – shows each column, it’s position and datatype

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

Examples of catalog objects

information_schema.tables – each object, 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 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

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

-- 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
group by 
  sobj.schema_id, 
  sobj.name
order by 
  table_name

-- SQL Server

use mydatabase -- put your DB here

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

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;

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

-- SQL Server

use mydatabase -- put your DB here

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 
  column_name, 
  data_type 
from 
  information_schema.columns 
where 
  table_schema = 'myschema';

-- PostgreSQL

select

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

-- SQL Server

use mydatabase -- put your DB here

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

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

-- SQL Server

use mydatabase -- put your DB here

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

-- PostgreSQL

select

table_name

from

information_schema.tables

where

table_schema = 'myschema' -- put your schema here

order by

table_name

Show the 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)

-- SQL Server

use mydatabase -- put your DB here

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

-- PostgreSQL

select

schemaname,

count(tablename)

from

pg_tables

group by

schemaname

order by

schemaname

Photo by Clement Nivesse from Pexels