Metadata queries are really helpful in discovering information for a given database schema. Database information including the tables, views, columns names, data types, indexes, and table constraints are all available using queries such as these.
During this tutorial, I want to explore some useful metadata queries.
Let us start by finding the list of tables created in the given database.
select * from information_schema.tables where table_type='base table';
Now let us list the views created in the given database.
select * from information_schema.tables where table_type='view';
Let us create a query that lists the column names, data types, whether the column allows null or not, and the maximum allowed characters in the row.
select column_name, data_type, is_nullable, character_maximum_length from information_schema.columns where table_name='emp';
This query shows the table name, object id, table creation date, and the last table modified time.
select name, object_id, create_date, modify_date from sys.tables;
Listing the created indexes for a table with the column names is frequently required. In this query a.name is the table name for which you are listing the indexes. By removing the a.name condition, you can see all the created indexes in your database.
SELECT a.name table_name, b.name index_name, d.name column_name FROM sys.tables a, sys.indexes b, sys.index_columns c, sys.columns d WHERE a.object_id = b.object_id AND b.object_id = c.object_id AND b.index_id = c.index_id AND c.object_id = d.object_id AND c.column_id = d.column_id AND a.name = 'emp';
This query will list the defined constraints on tables with the column names. In thie example, we can see the emp table’s unique, primary or foreign key constraints.
SELECT a.table_name, a.constraint_name, b.column_name, a.constraint_type FROM information_schema.table_constraints a, information_schema.key_column_usage b WHERE a.table_name = 'EMP' AND a.table_name = b.table_name AND a.table_schema = b.table_schema AND a.constraint_name = b.constraint_name;
Suppose you want to write a ‘select count(1) from table_name’ query for each table in your database, but you have more than 100 tables in your database. Instead of writing a separate query for each table, you can generate those queries using SQL. Therefore, you can write SQL code to generate SQL.
SELECT 'select count(1) from [' + table_name + '];' FROM information_schema.tables;