Database Files & Objects – Part 3

Today, I will be continuing my talk on database objects that can be viewed through the system catalog views or system stored procedures. A Microsoft SQL Server database is composed of many different objects. We will be reviewing how to retrieve information on these objects in the [MATH] sample database.

Please see my first article that creates this database. I will be focusing on tables, columns, indexes, and constraints in this article.

Let’s use the USE statement to select the sample database.

The most basic component of a database are tables. There are three distinct ways to get information about table structures.

If we want to get a simple list of all the tables in a database, one can execute the sp_tables system stored procedure. This returns all tables and views in the current database. Most of the 404 objects
are system tables and views.

We can filter the information by calling the stored procedure with the name of the table that we are interested in.

The system catalog views, most of the time, supply more information than these legacy system store procedures. If you query the sys.tables catalog view, you will see each table in the database. Nice thing about this view is that it only returns user defined tables. Detailed information on object id, schema id, principle id, and etc will be shown. The schema id can be used to obtain the schema name and the principle id will lead you to the owner of the table.

A more generic stored procedure that returns a wealth of information is sp_help. This takes an object name as a parameter and returns a bunch of information. In our example we get the table name, columns, indentity column, rowguid column, constraints, indexes, primary and foreign keys. Wow, make sure you change your output to text to see it all in one viewing.

large screen shot #1

The most basic component of a table are columns. There are two ways to get information on them. The first way is to call the sp_columns with our table name as a parameter. This is an important filter not to forget.

The second way, a query of the sys.columns catalog view will show all columns for a given table or view. We can use the OBJECT_ID() system function to return a id given a table or view name. However, the system types are encoded in this view. An additional join to the sys.types will decode the values into names.

It is very import to filter by object id since table-valued assembly functions (FT), inline table-valued SQL functions (IF), internal tables (IT), system tables (S), table-valued SQL functions (TF), user tables (U), and views (V) are all considered to have columns. Otherwise, we would end up with 8597 rows for each column in the database. Most columns are inherited system objects.

For good performance on adhoc queries, a table should be indexed with a primary key. We can use the sp_helpindex system stored procedure to get information on this index.

Like most examples today, there is a system catalog named sys.indexes view that can be queried to get the same information.

To demonstrate foreign keys, I am going to alter the [MATH] sample database to have a table that contains a sub-set of primes. The TSQL snippet below creates a new table with a calculated column, a foreign key and a identity column. It loads the new table with the top most 500 prime numbers.

large screen shot #2

With this new table comes the opportunity to look at two more column types. A query against the system catalog named sys.computed_columns will show us the [MY_SEARCH_PCT] column.

A query against the system catalog named sys.identity_columns will show us the [MY_ID] column.

While a primary key is a clustered index by default, it is also has an uniqueness condition. You can not have two records with the same primary key. This violates 3rd normal form. We can use the sp_pkeys to examine the primary key on a named table.

Again, there is a system catalog view named sys.key_constraints that can be queried to get information on primary or unique constraints.

A constraint can be classified as default, check, unique, foreign or primary. Since we have already talked about primary keys, we are going to examine a couple of the other types.

The sys.check_constraints view can be used to see what check constraints exist in the current database.

The sys.default_constraints view can be used to see what default constraints exist in the current database.

The sys.foreign_keys view can be used to see what foreign constraints exist in the current database.

The sys.objects view is the catch all place for objects in the database. If we filter on the various types of constraints: C – check constraint, D – default constraint, UQ – unique constraint, F – foreign key constraint, PK – primary key constraint, we can get detailed information that we saw in the previous stored procedure calls.

Last but not least, the sp_helpconstraint stored procedure can be used to return information on all constraints given a table name.

To recap this discussion, system stored procedures can be used grab information about the databases object such as tables, columns, indexes, and constraints. Sometimes, system catalog views have more detailed information on the objects you want to research. In the future, I am sure I will be designing T-SQL scripts using these commands to do maintenance tasks where third party vendors come up short.

Next time, I will be talking about using system stored procedures and catalog views to look at the following database objects: synonyms, views, functions, triggers and stored procedures.

Related posts

Leave a Comment