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,

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.

 

--all check constraints in the database and their parent table

SELECT name AS Name_of_Check_Constraint,   

object_schema_name(parent_object_ID)+'.'+object_name(parent_object_ID) AS parent

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsCheckCnst')= 1;

 

--all constraints in the database and their parent table

SELECT name as Name_of_Contraint, --see all constraints and parent table

object_schema_name(parent_object_ID)+'.'+object_name(parent_object_ID) AS parent

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsConstraint')= 1;

 

--all old-style defaults in the database

Select name --see all Sybase-style defaults

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsDefault')= 1;

 

--all default constraints in the database and their parent table

SELECT name AS Name_of_Default_Constraint,--see all Default constraints and parent table

object_schema_name(parent_object_ID)+'.'+object_name(parent_object_ID) AS parent

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsDefaultCnst')= 1;

 

--all executables (procedures, functions etc) in the database

SELECT name AS Name_Of_Executable, replace(lower(type_desc),'_', ' ') AS Type_Of_Executable

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsExecuted')= 1;

 

--all extended stored procedures in the database

SELECT name AS Name_of_Extended_Procedure

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsExtendedProc')= 1;

 

--all inline functions in the database

SELECT name AS Inline_function

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsInlineFunction')= 1;

 

--all primary keys in the database and their parent table

SELECT name AS Primary_key,

object_schema_name(parent_object_ID)+'.'+object_name(parent_object_ID) AS parent

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsPrimaryKey')= 1;

 

--all stored procedures in the database

SELECT name AS Stored_procedure

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsProcedure')= 1;

 

--all queues in the database

SELECT name AS QueueName

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsQueue')= 1;

 

--all old-style rules in the database

SELECT name AS RuleName  --old-fashioned sybase-style rule

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsRule')= 1;

 

--all scalar functions in the database

SELECT name AS Scalar_function

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsScalarFunction')= 1;

 

--all system tables in the database

SELECT name AS System_table

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsSystemTable')= 1;

 

--all tables, including system tables, in the database

SELECT name AS TableName --includes system tables etc

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsTable')= 1;

 

--all table-valued functions in the database

SELECT name AS Table_Valued_Function

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsTableFunction')= 1;

 

--all triggers in the database and their parent table

SELECT name AS [TriggerName],

object_schema_name(parent_object_ID)+'.'+object_name(parent_object_ID) AS parent

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsTrigger')= 1;

 

--all unique constraints in the database and their parent table

SELECT name AS Unique_constraint,--all unique constraints

object_schema_name(parent_object_ID)+'.'+object_name(parent_object_ID) AS parent

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsUniqueCnst')= 1;

 

--all user tables in the database

SELECT name AS User_Table

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsUserTable')= 1;

 

--all views in the database

SELECT name AS ViewName

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsView')= 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: 2435 | Views in the last 30 days: 21
 
Related Articles
FORUM

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

Schema Objects History for all Databases

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

ARTICLE

INFORMATION_SCHEMA.101

Get information about the objects in your database using these ANSI standard, multi-platform views.

Tags
metadata    
stairway series    
 
Contribute