SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Dynamic Online Catalog: Why Should You Care? Stairway to Exploring Database Metadata Level 1

By Phil Factor, (first published: 2016/08/03)

The Series

This article is part of the Stairway Series: Stairway to Exploring Database Metadata

In addition to the data that our clients and customers store in a database, there is a tremendous amount of meta data, 'data about data', that describes how the database is set up, configured, and what the properties are for the various objects. This stairway aims to demystify and explain how you can query and use this meta data to more effectively manage your SQL Server Databases.

  1. What is the Dynamic Online Catalog?
  2. How do we get to the information?
  3. The System Views
    1. The Information Schema
    2. The Compatibility Views
    3. The Catalog Views
    4. The Data-tier Application Views
    5. Dynamic Management Views and Functions (DMVs)
  4. The Metadata functions
  5. Why are metadata views and functions important?
  6. How do I get started?
  7. Commonplace examples of use.
    1. Where’s my stuff?.
  8.  

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. .

 

This article is part of the Stairway to Exploring Database Metadata Stairway

Sign up to our RSS feed and get notified as soon as we publish a new level in the Stairway! Rss

Total article views: 3176 | Views in the last 30 days: 73
 
Related Articles
FORUM

how to check the Schema Objects Details in Each Database using SP

Schema Objects History for all Databases

BLOG

Day 12 INFORMATION_SCHEMA database

Yesterday we discuss about mysql system database, today we will see “INFORMATION_SCHEMA” database, t...

ARTICLE

Querying the INFORMATION_SCHEMA

Using a macro to query the INFORMATION_SCHEMA

BLOG

Database Fundamentals #9: Schemas As Containers

Schemas are a very useful tool for managing the objects in your database. From security through proc...

BLOG

Drop That Schema

An often under utilized or maybe even mis-utilized feature of SQL Server is a database object sche...

Tags
metadata    
stairway series    
 
Contribute