Categories
SQL 201

6 Ways to Search With System Tables

Your database doesn’t just contain your data. It also contains data about your data.


Your database doesn’t just contain your data.

It also contains data about your data in System Tables.


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




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.




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.




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.




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.




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.




Picture by Pixabay On Pexels

By Helen Anderson

I’m passionate about technology and building data applications.