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.

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.

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.

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.View more articles by Vishwanath Dalvi