This stairway is intended to appeal to a range of expertise and knowledge in SQL Server. Some of it will seem obvious to you, but I aim to add information that will be new to even experienced DBAs or database developers: After all, some of the information surprised me. I will, however, be assuming a reasonable working knowledge of database objects and other types of metadata; in other words, what a View, Table, Function, Procedure, Schema, constraint and so on actually is. After some introductory theory, I aim to try to illustrate as much as possible with practical and immediately-useful code.
What is the Dynamic Online Catalog?
Every relational database system such as SQL Server must provide information about its structure through the same SQL syntax that is used to access the data. This is usually accomplished by storing the structure definition within special system tables. This means that there are two types of tables within a database—user tables that contain the 'working' data and system tables or views that contain metadata. Starting with SQL Server 2005, no system base tables, only views, are exposed to the user, and they can be seen in SSMS within the object browser.
Figure 1 shows some of these information schema and system views.
Figure 1: Some of the system views
The collection of system tables/views is usually referred to in the literature on relational database theory as the system catalog or data dictionary.
It seems recursive, but deep inside SQL Server, there are system tables that are used to keep track of everything in our databases. These system tables store information about such database ‘things’ as the tables, routines, columns and indexes. This is all done to conform to one of Edgar Codd’s thirteen rules for the implementation of a relational database. This rule defines the Dynamic Online Catalog which is the data about the data, otherwise known as the ‘metadata’.
Edgar Codd’s rule 4, ‘Dynamic online catalog based on the relational model’ states
‘The database description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data.’
In SQL Server, the information from the Dynamic Online Catalog cannot be accessed directly but is read either via System Views or Information Schema Views. This stairway is intended to show you how to use SQL Server’s Dynamic Online Catalog via SQL Server’s System Views and ‘Information Schema Views’ in order to make it easier to develop and manage a SQL Server database.
How do we get to the information?
Because we can’t access the Dynamic Online Catalog directly, we use views and functions to see the information in it. You can only see the information that you are entitled to see. You can’t directly change the data in the catalog either, because you have the better approach of using Data Definition Language (DDL) within a user database. These SQL DDL statements include CREATE, DROP, ALTER, GRANT, DENY, REVOKE and sp_rename statements to make changes. There is always a way, using DDL, to alter any of the information in the catalog views, even if is not always obvious.
Relational databases are obliged to use an Dynamic Online Catalog to describe a database, but there is no standardisation of the precise way of going about it. There is, however a way of reading this information via a special schema that is included with every database: the Information Schema.
Unfortunately, this schema does not always provide sufficient information for our purposes because the suppliers of RDBMSs are not pestered to keep it to the standard. This means that we have no choice but to supplement this Information Schema by using SQL Server’s System Catalog views and functions.
This Stairway will take a task-oriented approach to finding out about what is in your databases. Before we start with the practical details, though, I’ll need to explain a few terms and techniques in as little detail as is sufficient to get you through the examples easily.
Figure 2 illustrates the interfaces that are provided to access the metadata held within SQL Server. I will then describe each of these terms.
Figure 2: Interfaces used to access SQL Server’s metadata
The System Views
The Information Schema
The Information Schema is a set of views with information about the objects that are in the current database. This schema exists in all databases. You can only see the information on objects that you are entitled to view within the current database. The information schema is fairly standard across RDBMSs because it is defined in the SQL standard. Your code that uses the Information Schema is likely to be portable across all the major RDBMSs. The Information Schema Views do not currently hold information that is specific to SQL Server’s implementation, though the standard allows this to be done.
The Information Schema is particularly important for processes that need to work across different RDBMSs, They are excellent for routine work, such as checking whether a table exists before accessing it, but they are limited in their value when you need detailed reports about your database. They also use a slightly different standard nomenclature: A database, for example, is called a catalog, and a user-defined data type is called a ‘domain’.
There is a scary message on MSDN with this warning: ‘Do not use INFORMATION_SCHEMA views to determine the schema of an object’. This message doesn’t mean that the schema information can be wrong, but it refers to the practice of trying to use these views to work out the schema when knowing only the name of the table. SQL Server allows you to have the same table name in different schemas. Despite this scary message, the Information Schema is perfectly OK to use.
The Compatibility Views
The Compatibility Views maintain the view of the metadata that was available as system tables prior to SQL Server 2005 and are provided for backward compatibility only. Any queries of these system tables will still run but nothing that was introduced in SQL Server 2005 onwards is represented, such as table partitions, so only part of your metadata or its characteristics may be visible to you. For large databases with more users, groups, and roles, or data types allowed by SQL Server 2000, they are potentially dangerous to use because the compatibility view columns that store user IDs and type IDs may return NULL or trigger arithmetic overflows.
The Catalog Views
Catalog views provide us with information about the structure of a database. They are also used by the SQL Server Database Engine itself, particularly in the query optimiser, so are designed to be the most efficient way of getting information about the metadata. All metadata is exposed through these catalog views except for replication, backup, database maintenance plan, or for SQL Server Agent catalog data.
The catalog views are arranged in a rather special way, in that all the basic information that all SQL Server objects have in common is held in sys.objects. There are many derived views, such as those for foreign keys, constraints, service queues, tables, views and procedures that supplement the general object information with information that is specific to the type of object being catalogued.
Not everything in SQL Server metadata is an object. A column, index or distribution statistic isn’t an object, for example. Some things such as primary key constraints or extended properties have a strange half-life in that they are represented as an object but instantiated by an index that enforces the key, but which isn’t an object. Some objects, mainly constraints, have a parent/child relationship with another type of object; the table.
The Data-tier Application Views
Data-tier application views are used purely to give access to information about registered servers. Servers are registered at a particular version and the information in these views is used to check whether the version has ‘drifted’. It is the easiest way of checking the current registered version of a database using T-SQL.
Dynamic Management Views and Functions (DMVs)
Dynamic Management Views and Functions (DMVs) are used to tune performance, diagnose problems and monitor the health of either the server or database. They aren’t intended for accessing metadata or allied information, but in the context of this stairway they are valuable to introduce because they give information about the way that SQL Server is being used. This allows us, for example, to not only list indexes, but to list them in the order of the amount of usage that they get.
The Metadata functions
There are also a whole lot of metadata functions such as object_name(), or col_name() that provide information about schema-scoped objects in the current database. They provide short-cut ways of getting information by avoiding making explicit joins in your metadata expressions and so are a great help in getting at information about metadata more quickly when used together with the catalog views.
The Catalog Stored Procedures
There are a number of stored procedures whose primary function is to provide metadata information to the ODBC driver for SQL Server. When you make an ODBC connection, this information is made available as a collection of data objects. However, the information is available generally, and can be used from SQL just like any other stored procedure. They are usually considered less useful than catalog views because the result returned by a stored procedure has to be explicitly inserted into a table or table variable using the INSERT ... EXECUTE syntax. They also have only a subset of the information available in a catalog view.
Why are metadata views and functions important?
The metadata views and functions allow you to search for metadata things, to give you reports and summaries about your database, to work out who has permission to view or alter what data, to allow you to cut down repetitive typing, to get you almost all the information buried somewhere inside SQL Server Management Studio, to make deployment scripts safer and more reliable, to find out what’s been recently altered or created, to quickly work on a number of functions or procedures, to determine the version of a registered database, to audit database code for coding practices, discover duplicate indexes, and generally allow you to cut down on inefficient ‘point ‘n click’ operations. When combined with other SQL Server facilities such as the default trace and dynamic management objects, it is reasonably quick to throw together SQL scripts of immense power for developing and managing databases.
The metadata views and functions allow you to do things that are almost impossible to do any other way, such as finding the parameters that depend on a specified CLR user-defined type or alias type.
How do I get started?
The first stage of learning to use metadata views and functions is to collect queries that use them from a variety of reputable sources such as SQL Server Central, Simple-Talk or MSDN. Hoard these queries using a tool for keeping notes. It helps if it is a tool for keeping notes or snippets that allows you to get at the queries easily wherever you are. After a while, you’ll start changing them slightly for your particular requirements. Then, instead of searching through a list of tables in the object browser pane, you’ll soon be able to just grab an appropriate query from your collection, execute it, and get the information quickly.
Commonplace examples of use.
In all these examples, I’ve tested them on SQL Server 2008 and 2012. Where the example is compatible only with later versions, I’ll mention that.
Where’s my stuff?
The catalog view sys.objects contains useful information about all the bits and pieces of the database such as tables, views, triggers, functions and procedures. Some of these ‘objects’ require additional data so views are provided that inherit from sys.objects but which provide extra information particular to that type of object. This applies to synonyms, foreign keys, check constraints, key constraints, default constraints, service queues, tables, views and procedures. These can be accessed in the catalog views sys.synonyms, sys.foreign_keys, sys.check_constraints, sys.key_constraints, sys.default_constraints, sys.service_queues, sys.tables, sys.views and sys.procedures..
Figure 3 displays all the views that inherit the columns of sys.objects. This means that they have the columns of sys.objects and in addition, columns relevant just to their type of object. This means that these views all have information such as create_date and modify_date that come from sys.objects.
Figure 3: All of the catalog views that depend on sys.objects
To list all the views in your database just do this …
SELECT object_schema_name(object_id)+'.'+name FROM sys.views;
Or for listing all your stored procedures …
SELECT object_schema_name(object_id)+'.'+name FROM sys.procedures;
… or your foreign keys
SELECT name AS Foreign_key,
object_schema_name(parent_object_ID)+'.'+object_name(parent_object_ID) AS parent,
object_schema_name(referenced_object_ID)+'.'+object_name(referenced_object_ID) AS referenced
FROM sys.foreign_keys;
For all the others, you’ll need to use a system function to filter just those objects that you want. The code below provides several example that should be useful. Because we are just getting the name of the objects, we are using sys.objects, a view that has the basic information that is common to all database objects. If we needed information that is specific to a particular type of object, such as whether a primary key has a system-generated name, you’d have to use the view for that particular type of object.
/* The Tables */ --all user tables in the database
SELECT
ob.name AS User_Table, Coalesce(ep.value, '') AS documentation
FROM sys.objects AS ob
LEFT OUTER JOIN sys.extended_properties AS ep
ON ep.major_id = ob.object_id
AND ep.class = 1
AND ep.minor_id = 0
WHERE ObjectProperty(ob.object_id, 'IsUserTable') = 1
/* The Views */ --all views in the database
SELECT ob.name AS ViewName, Coalesce(ep.value, '') AS documentation
FROM sys.objects ob LEFT OUTER JOIN sys.extended_properties AS ep
ON ep.major_id = ob.object_id
AND ep.class = 1
AND ep.minor_id = 0
WHERE objectproperty(ob.object_id,'IsView')= 1
/* The Check Constraints */ --all the check constraints in the database
SELECT
objects.name AS Name_of_Check_Constraint,
Object_Schema_Name(objects.parent_object_id) + '.' + Object_Name(objects.parent_object_id) AS parent,
Coalesce(ep.value,'') AS documentation
FROM sys.objects
LEFT OUTER JOIN sys.extended_properties AS ep
ON ep.major_id = objects.object_id AND ep.class=1
AND ep.name='MS_Description'--the microsoft convention
WHERE ObjectProperty(objects.object_id, 'IsCheckCnst') = 1
/* The Constraints */
SELECT
--all the constraints in the database
objects.name AS Name_of_Constraint, --see all constraints and parent table
Lower(Replace(type_desc,'_',' ')),--the type of constraint
Object_Schema_Name(objects.parent_object_id) + '.' + Object_Name(objects.parent_object_id) AS parent,
Coalesce(ep.value, '') AS documentation
FROM sys.objects
LEFT OUTER JOIN sys.extended_properties AS ep
ON ep.major_id = objects.object_id
AND ep.class = 1
AND ep.name = 'MS_Description' --the microsoft convention
WHERE ObjectProperty(objects.object_id, 'IsConstraint') = 1;
/* The Defaults */--all old-style defaults in the database
SELECT
objects.name, --see all Sybase-style defaults
Coalesce(ep.value, '') AS documentation
FROM sys.objects
LEFT OUTER JOIN sys.extended_properties AS ep
ON ep.major_id = objects.object_id
AND ep.class = 1
AND ep.name = 'MS_Description' --the microsoft convention
WHERE ObjectProperty(objects.object_id, 'IsDefault') = 1;
/* The Default Constraints */--all default constraints in the database and their parent table
SELECT objects.name AS Name_of_Default_Constraint,--see all Default constraints and parent table
Coalesce(ep.value,'') AS documentation,
object_schema_name(objects.parent_object_id)+'.'+object_name(objects.parent_object_id) AS parent,
Coalesce(EP_parent.value,'') AS documentation
FROM sys.objects
LEFT OUTER JOIN sys.extended_properties AS ep
ON ep.major_id = objects.object_id
AND ep.class = 1
AND ep.name = 'MS_Description' --the microsoft convention
LEFT OUTER JOIN sys.extended_properties AS EP_parent
ON ep.major_id = objects.parent_object_id
AND ep.name = 'MS_Description' --the microsoft convention
WHERE objectproperty(objects.object_id,'IsDefaultCnst')= 1;
/* The Executables */--all executables (procedures, functions etc) in the database
SELECT
oe.name AS Name_Of_Executable,
Replace(Lower(oe.type_desc), '_', ' ') AS Type_Of_Executable,
Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS oe
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = oe.object_id
AND EP.name = 'MS_Description' --the microsoft convention
WHERE ObjectProperty(oe.object_id, 'IsExecuted') = 1;
/* The Extended Stored Procedures */
--all extended stored procedures in the database
SELECT
oep.name AS Name_of_Extended_Procedure, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS oep
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = oep.object_id
AND EP.name = 'MS_Description' --the microsoft convention
WHERE ObjectProperty(oep.object_id, 'IsExtendedProc') = 1;
/* The Inline Functions */
--all inline functions in the database
SELECT ilf.name AS Inline_function,
Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS ilf
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = ilf.object_id
AND EP.name = 'MS_Description' --the microsoft convention
WHERE objectproperty(ilf.object_id,'IsInlineFunction')= 1;
/* The Primary Keys */
--all primary keys in the database and their parent table
SELECT
pk.name AS Primary_key,
Object_Schema_Name(pk.parent_object_id) + '.' + Object_Name(pk.parent_object_id) AS parent,
Coalesce(EP.value, '') AS KeyDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS pk
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = pk.object_id
AND EP.name = 'MS_Description' --the microsoft convention
LEFT OUTER JOIN sys.extended_properties AS EPParent
ON EPParent.major_id = pk.parent_object_id
AND EPParent.minor_id = 0
AND EPParent.name = 'MS_Description' --the microsoft convention
WHERE ObjectProperty(pk.object_id, 'IsPrimaryKey') = 1;
/* The Stored Procedures */--all stored procedures in the database
SELECT
sp.name AS Stored_procedure, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS sp
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = sp.object_id
AND EP.minor_id = 0
AND EP.name = 'MS_Description' --the microsoft convention
WHERE ObjectProperty(sp.object_id, 'IsProcedure') = 1;
/* The Queues */
--all queues in the database
SELECT
q.name AS QueueName, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS q
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = q.object_id
AND EP.name = 'MS_Description' --the microsoft convention
WHERE ObjectProperty(q.object_id, 'IsQueue') = 1;
/* The Rules */
--all old-style rules in the database
SELECT
ru.name AS RuleName, --old-fashioned sybase-style rule
Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS ru
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = ru.object_id
AND EP.name = 'MS_Description' --the microsoft convention
WHERE ObjectProperty(ru.object_id, 'IsRule') = 1;
/* The Scalar Functions */
--all scalar functions in the database
SELECT
sf.name AS Scalar_function, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS sf
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = sf.object_id
AND EP.name = 'MS_Description' --the microsoft convention
WHERE ObjectProperty(sf.object_id, 'IsScalarFunction') = 1;
/* The System Tables */
--all system tables in the database
SELECT
st.name AS System_table, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS st
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = st.object_id
AND EP.name = 'MS_Description' --the microsoft convention
WHERE ObjectProperty(st.object_id, 'IsSystemTable') = 1;
--all tables, including system tables, in the database
SELECT
at.name AS TableName, --includes system tables etc
Lower(Replace(type_desc,'_',' ')),--the type of constraint
Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS at
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = at.object_id
AND EP.name = 'MS_Description' --the microsoft convention
WHERE ObjectProperty(at.object_id, 'IsTable') = 1;
/* The TVFs*/
--all table-valued functions in the database
SELECT
tvf.name AS Table_Valued_Function, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS tvf
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = tvf.object_id
AND EP.name = 'MS_Description' --the microsoft convention
WHERE ObjectProperty(tvf.object_id, 'IsTableFunction') = 1;
--all triggers in the database and their parent table
SELECT
tr.name AS TriggerName,
Object_Schema_Name(tr.parent_object_id) + '.' + Object_Name(tr.parent_object_id) AS parent,
Coalesce(EP.value, '') AS TriggerDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS tr
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = tr.object_id
AND EP.name = 'MS_Description' --the microsoft convention
LEFT OUTER JOIN sys.extended_properties AS EPParent
ON EPParent.major_id = tr.parent_object_id
AND EPParent.minor_id = 0
AND EPParent.name = 'MS_Description' --the microsoft convention
WHERE ObjectProperty(tr.object_id, 'IsTrigger') = 1;
/* The Unique Constraints */
--all unique constraints in the database and their parent table
SELECT uc.name AS Unique_constraint,--all unique constraints
object_schema_name(uc.parent_object_id)+'.'+object_name(uc.parent_object_id) AS parent,
Coalesce(EP.value, '') AS ConstraintDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS uc
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = uc.object_id
AND EP.name = 'MS_Description' --the microsoft convention
LEFT OUTER JOIN sys.extended_properties AS EPParent
ON EPParent.major_id = uc.parent_object_id
AND EPParent.minor_id = 0
AND EPParent.name = 'MS_Description' --the microsoft convention
WHERE objectproperty(uc.object_id,'IsUniqueCnst')= 1;
We can, of course, tweak these to give us precisely the information we want: For example:
--all views in the database modified within the past two weeks
SELECT name AS ViewName, convert(char(11),modify_date,113)
FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsView')= 1
AND modify_date > dateadd(week,-2, GetDate());
--the names and type of all objects created in the past month
SELECT coalesce(object_schema_name(obj.object_ID)+'.','')+obj.name AS ObjectName,
convert(varchar(30),lower(replace(type_desc,'_',' ')))
FROM sys.objects obj
WHERE create_date > dateadd(month,-1, GetDate());
--the names and type of all base objects in the DBO schema
SELECT coalesce(object_schema_name(obj.object_ID)+'.','')+obj.name AS ObjectName,
convert(varchar(30),lower(replace(type_desc,'_',' '))) as ObjectType
FROM sys.objects obj
WHERE parent_object_ID=0
AND schema_ID = schema_ID('dbo');
So that’s enough for one level. We’ve gone over the general theory and introduced the basic ways of finding what is in your database. In the next level of this stairway, we’ll be exploring triggers in more depth and finding out the sort of useful information that we can glean from the Dynamic Online Catalog via system views. .