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

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 1 2 3 4 5 6 7 8 9 10 11 12 13 14 -- 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; 1 2 3 4 5 6 7 8 9 10 11 12 13 -- 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 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 -- 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; 1 2 3 4 5 6 7 8 9 10 11 12 -- 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 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 -- 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 column_name, data_type from information_schema.columns where table_schema = 'myschema'; 1 2 3 4 5 6 7 8 9 -- 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 1 2 3 4 5 6 7 8 9 10 11 12 13 -- 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 1 2 3 4 5 6 7 8 9 10 -- 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] 1 2 3 4 5 6 7 8 9 10 11 12 -- 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 1 2 3 4 5 6 7 8 9 10 -- 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) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 -- 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 1 2 3 4 5 6 7 8 9 10 11 -- PostgreSQL select schemaname , count ( tablename ) from pg_tables group by schemaname order by schemaname

Photo by Jenna Hamra from Pexels

Sharing is caring!