Exploring Database Schemas on SQL Server

Introduction

There is very little you can do with a database without knowing the schema. SQL Server Management Studio has a tree view that lets you see the tables, views and other objects in the database, but drilling down in this list to find the information you are interested in can be fiddly and time-consuming. Here I’ll show you a number of alternative ways to get at it. We’ll look at what is possible with SSMS, how to extract data from the INFORMATION_SCHEMA views, and how to quickly see the information using Red Gate SQL Prompt. You may want to download a free trial of SQL Prompt and follow along.

INFORMATION_SCHEMA Views

Before looking at some examples, I should explain a bit more about INFORMATION_SCHEMA views. These are part of the SQL92 standard and were first introduced in SQL Server 7.0. Because they are part of the SQL standard, they sometimes use generic terms rather than the ones used elsewhere in SQL Server. The most important difference is that databases are referred to as Catalogs.

The INFORMATION_SCHEMA views contain information about the schema of each database. They only include areas covered by the SQL standard, so there are a number of SQL Server features they do not include. For these features, you have to look in the system tables. INFORMATION_SCHEMA views have two advantages. Unlike system tables, they are guaranteed not to change significantly between server versions, so any scripts you write using them should not break when the next service pack is released. They are also supported by a range of different database engines, including SQL Server, MySQL and PostgreSQL.

Finding All Tables and Views

When trying to get my head around a new database, I usually begin by skimming through a list of all the available tables and views. In management studio, you can view this information either by using the object explorer tree view and looking at the lists under “Tables” and “Views” for the database you are interested in, or by doing the same using the Object Explorer Details tab. For all but the smallest databases, the details tab is the way to go. You can also drag the names of tables into the query window to insert them into a script.

You can extract a list of tables and views from the INFORMATION_SCHEMA using:

SELECT * FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_TYPE

This will bring up a list of all the tables and views in the current database. Tables will have a table type of BASE TABLE; views will have a type of VIEW. You can restrict this list to a particular schema by filtering on the TABLE_SCHEMA column.

To bring up a list of all tables with SQL Prompt, type SELECT * FROM into the query editor pane. The candidate list will appear with a list of all tables and views. If you wish to filter to see only tables or only views, you can use the filter icons below the list. This list can also be brought up by pressing Ctrl+Space in an empty window. This is by far the quickest way to get a list of tables, but you cannot copy the list to the clipboard. So if you need to do further processing, you should use INFORMATION_SCHEMA.

Lists of Columns

Whilst it may be very interesting to just look at a list of all the tables in our database, to do anything useful we need to know a bit more about their contents: the columns. In SSMS, this is available through the object explorer tree under each table. Data type, nullability, and whether the column is a key are displayed for each. You can also drag the Columns folder itself from the tree to the query editor. This will insert a comma-separated list of all the columns in the table.

In SQL Server, a list of columns is also available using the sp_help stored procedure. If you pass this procedure the name of a table, it will return a range of information including keys, indexes, constraints and a list of all the columns. It is very useful when you want to quickly learn about a single table.

Unsurprisingly, you can get a list of columns from the INFORMATION_SCHEMA.COLUMNS view. This view includes one row for each column in the current database. If there are columns with the same name in more than one table, they will appear as multiple separate rows. If you are only interested in the columns from one table, you can filter by table name. For example (based on the AdventureWorks database):

SELECT * FROM INFORMATION_SCHEMA.COLUMNS AS c WHERE c.TABLE_SCHEMA = 
    'Person' AND c.TABLE_NAME = 'Address'

There are about 20 columns in the results returned. The most interesting ones are COLUMN_NAME, DATA_TYPE and COLUMN_DEFAULT, which all contain what you would expect. Most of the other columns only apply to particular data types. For example, there is a column specifying the precision of DATETIME fields. Unless you are looking at a DATETIME, this will be null.

Using the TABLES and COLUMNS views together -- along with a trick that uses FOR XML PATH to do string concatenation (SQL Server 2005+ only) -- we can get a list of all tables, with a comma-separated list of columns for each. The query looks like this:

SELECT  t.TABLE_SCHEMA,
        t.TABLE_NAME,
        STUFF(( SELECT  ',' + COLUMN_NAME
                FROM    INFORMATION_SCHEMA.COLUMNS AS c
                WHERE   c.TABLE_SCHEMA = t.TABLE_SCHEMA
                        AND c.TABLE_NAME = t.TABLE_NAME
                ORDER BY c.COLUMN_NAME
              FOR
                XML PATH('')
              ), 1, 1, '') AS Columns
FROM    INFORMATION_SCHEMA.TABLES AS t

Here we look up a list of tables and do a correlated subquery on the COLUMNS view to find out all the columns contained in that table. FOR XML PATH(‘’) causes all the results to be concatenated into a single value. The STUFF function simply removes the leading comma that would otherwise appear at the start of the list.

Another common task is to identify all tables that contain a column with a particular name: for example, all tables that have a ContactID column. This is useful for databases which don’t have foreign keys set up for all relationships. It is straightforward to pull this list from the COLUMNS view.

SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM 
INFORMATION_SCHEMA.COLUMNS AS c WHERE COLUMN_NAME = 'ContactID'

SQL Prompt makes column information available in a number of ways. Firstly, if you have a wildcard select query (e.g. SELECT * FROM Person.Address), if you position the cursor next to the asterisks and press Tab, SQL Prompt will insert a complete list of columns in that table. You can also hover over the table name to see a schema description and a list of the columns the table contains.

Keys and Relationships

In databases where foreign key relationships have been created between tables, they provide a good way to see how different tables are related. In SSMS, you can view the keys associated with each table in the Keys folder under each table. However, this isn't hugely useful as it doesn't show you which tables and columns the keys reference without opening up a separate dialog. The INFORMATION_SCHEMA views let us pull out this information, but it is a little awkward.

The INFORMATION_SCHEMA views store information about three types of constraints: check constraints, primary keys and foreign keys. Some of the views store only information about some types of constraints, and some store information about all of them. A list of foreign keys (and only foreign keys) is kept in the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view. For each foreign key, this has the full name of the key (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA and CONSTRAINT_NAME) and the full name of the primary key it references (UNIQUE_CONSTRAINT_CATALOG, UNIQUE_CONSTRAINT_SCHEMA and UNIQUE_CONSTRAINT_NAME). It doesn’t contain any table or column information: for this, you need to look in another view.

Table and column information is stored in the INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view. To get full information about keys, we need to join to this twice: once for the foreign key and once for the primary key it references. A query to extract a list of all foreign key relationships from the database looks something like this:

SELECT  ccu.TABLE_SCHEMA + '.' + ccu.TABLE_NAME + '.' + 
    ccu.COLUMN_NAME AS ForeignKeyColumn,
        ccu2.TABLE_SCHEMA + '.' + ccu2.TABLE_NAME + '.' + 
    ccu2.COLUMN_NAME AS PrimaryKeyColumn
FROM    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
JOIN    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu
        ON rc.CONSTRAINT_CATALOG = ccu.CONSTRAINT_CATALOG
        AND rc.CONSTRAINT_SCHEMA = ccu.CONSTRAINT_SCHEMA
        AND rc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
JOIN    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu2
        ON rc.UNIQUE_CONSTRAINT_CATALOG = ccu2.CONSTRAINT_CATALOG
        AND rc.UNIQUE_CONSTRAINT_SCHEMA = ccu2.CONSTRAINT_SCHEMA
        AND rc.UNIQUE_CONSTRAINT_NAME = ccu2.CONSTRAINT_NAME

You can also use this to find all the tables that have foreign keys referencing a particular column. For example, to find all tables which reference Person.Contact.ContactID, you could use the following (wrapped as a stored procedure for easy further use):

CREATE PROCEDURE GetReferencingTables (
	@TableSchema VARCHAR(MAX),
	@TableName VARCHAR(MAX),
	@ColumnName VARCHAR(MAX) = NULL
) AS
SELECT  ccu.TABLE_SCHEMA + '.' + ccu.TABLE_NAME AS ChildTable
FROM    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
JOIN    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu
        ON rc.CONSTRAINT_CATALOG = ccu.CONSTRAINT_CATALOG
        AND rc.CONSTRAINT_SCHEMA = ccu.CONSTRAINT_SCHEMA
        AND rc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
JOIN    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu2
        ON rc.UNIQUE_CONSTRAINT_CATALOG = ccu2.CONSTRAINT_CATALOG
        AND rc.UNIQUE_CONSTRAINT_SCHEMA = ccu2.CONSTRAINT_SCHEMA
        AND rc.UNIQUE_CONSTRAINT_NAME = ccu2.CONSTRAINT_NAME
WHERE ccu2.TABLE_SCHEMA = @TableSchema
	AND ccu2.TABLE_NAME = @TableName
	AND (ccu2.COLUMN_NAME = @ColumnName OR @ColumnName IS NULL)
GO

-- Get all tables referencing Person.Contact.ContactID
EXECUTE GetReferencingTables 'Person', 'Contact', 'ContactID'

-- Get all tables referencing any column in Sales.Customer
EXECUTE GetReferencingTables 'Sales', 'Customer'

SQL Prompt makes join information available as you are typing queries. After to you type JOIN, it will present you with a list of other tables starting with those with likely joins to the table you are selecting from. This is based on both foreign keys and matching column names. It will then prompt you with appropriate complete join conditions between the two tables.

You should now have an idea about how you can explore databases with SSMS, the INFORMATION_SCHEMA views and Red Gate SQL Prompt, and when is appropriate to use each. For best results, you should have all three at your disposal so you can use whichever is the most suitable to help with the task at hand.

• MSDN Documentation on INFORMATION_SCHEMA
• More information and a free trial download of SQL Prompt