Retrieving Table Metadata from SQL Server Catalog Views

Introduction

All database systems that I’ve worked with have some sort of system tables that provide information about the tables, columns, indexes, constraints, etc in a database. SQL Server is no exception; in fact there are different ways of querying its System Catalog. We’re going to look at one of these: Catalog Views, which can be queried easily.

Being able to retrieve this metadata can be very useful in various situations, such as developing tools to work with and visualize data (like SQL Server Management Studio), or automating rote tasks (such as creating Entity classes for each table).

List of Table Names

Querying catalog views is as easy as querying any table. The easiest thing you can do is get a list of table names. Use this query:

select *
from sys.tables;

Here’s the result:

So if all you want is the name of the table, just refine the query to select only that:

select name
from sys.tables;

Retrieving data using ADO .NET

It is easy to run simple queries using the good old ADO .NET technology. Below is a sample you could use to retrieve the table names from a C# application.

static void Main(string[] args)
        {
            const string connStr = @"server=.\SQLEXPRESS;database=BookStore;Trusted_Connection=True;";
            using (var conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (var command = new SqlCommand())
                {
                    command.Connection = conn;
                    command.CommandText = "select name from sys.tables;";
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            string name = reader["name"].ToString();
                            Console.WriteLine(name);
                        }
                    }
                }
            }
            Console.ReadLine();
        }

This code can be similarly adapted to fetch additional data from the queries we will see next.

More Advanced Queries

Retrieving other table metadata such as indexes, foreign keys, etc is not as straightforward as retrieving table names. However, you can get queries for just about any metadata you need from the Querying the SQL Server System Catalog FAQ.

Here’s the query to get column data types:

SELECT c.name AS column_name
    ,c.column_id
    ,SCHEMA_NAME(t.schema_id) AS type_schema
    ,t.name AS type_name
    ,t.is_user_defined
    ,t.is_assembly_type
    ,c.max_length
    ,c.precision
    ,c.scale
FROM sys.columns AS c 
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE c.object_id = OBJECT_ID('<schema_name.table_name>')
ORDER BY c.column_id;

Here’s the result:

This is how you get the indexes for a table (which may include primary keys):

SELECT i.name AS index_name
    ,i.type_desc
    ,is_unique
    ,ds.type_desc AS filegroup_or_partition_scheme
    ,ds.name AS filegroup_or_partition_scheme_name
    ,ignore_dup_key
    ,is_primary_key
    ,is_unique_constraint
    ,fill_factor
    ,is_padded
    ,is_disabled
    ,allow_row_locks
    ,allow_page_locks
FROM sys.indexes AS i
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
WHERE is_hypothetical = 0 AND i.index_id <> 0 
AND i.object_id = OBJECT_ID('<schema_name.table_name>');

Here’s an example result:

And finally, here’s how you get info on the foreign keys:

SELECT 
    f.name AS foreign_key_name
   ,OBJECT_NAME(f.parent_object_id) AS table_name
   ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name
   ,OBJECT_NAME (f.referenced_object_id) AS referenced_object
   ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name
   ,is_disabled
   ,delete_referential_action_desc
   ,update_referential_action_desc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc 
   ON f.object_id = fc.constraint_object_id 
WHERE f.parent_object_id = OBJECT_ID('<schema_name.table_name>');

Here’s the result of that:

So even though these queries aren’t trivial to cook up, you can find just about anything you need from the Querying the SQL Server System Catalog FAQ, and just adapt it from there.

One Query to Rule Them

If you’re going to do something like code generation, you probably want to build one query that retrieves all the above metadata in one go. You can do that by combining the above queries. Fortunately, I’ve done that for you. Here you go:

SELECT
	-- columns / data types
	c.name AS column_name
    ,c.column_id
    ,SCHEMA_NAME(t.schema_id) AS type_schema
    ,t.name AS type_name
    ,c.max_length
    ,c.precision
    ,c.scale
	-- primary key / indexes
	,i.name AS index_name
    ,is_identity
	,i.is_primary_key
	-- foreign key
    ,f.name AS foreign_key_name
   ,OBJECT_NAME (f.referenced_object_id) AS referenced_object
   ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name
FROM sys.columns AS c 
INNER JOIN sys.types AS t
	ON c.user_type_id=t.user_type_id
LEFT OUTER JOIN sys.index_columns AS ic
	ON ic.object_id = c.object_id
	AND c.column_id = ic.column_id
LEFT OUTER JOIN sys.indexes AS i
	ON i.object_id = ic.object_id
	AND i.index_id = ic.index_id
LEFT OUTER JOIN sys.foreign_key_columns AS fc
	ON fc.parent_object_id = c.object_id
	AND COL_NAME(fc.parent_object_id, fc.parent_column_id) = c.name
LEFT OUTER JOIN sys.foreign_keys AS f
	ON f.parent_object_id = c.object_id
	AND fc.constraint_object_id = f.object_id
WHERE c.object_id = OBJECT_ID('dbo.Book')
ORDER BY c.column_id;

Here’s what you’ll get:

That includes column names, column types (along with stuff like decimal precision), indexes, primary keys, auto-increment (that’s is_identity), and foreign key info (constraint name, referenced table, and referenced column).

I’ve only tested this on a very simple scenario, so I’m pretty sure there are improvements to be made. While this can be considered a starting point, feedback is more than welcome.