6 Ways to Search
with System Tables


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

 

 

Bitnami