SQL Server: Useful Metadata queries

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;