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.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Which database to use. USE [MATH] go </span> |
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.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Older system stored proc (all tables & views) sp_tables go </span> |
We can filter the information by calling the stored procedure with the name of the table that we are interested in.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Older system stored proc - just one table sp_tables @table_name = 'TBL_PRIMES'; go </span> |
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.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- List tables (user tables) select * from sys.tables go </span> |
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.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Detailed information on the table sp_help @objname = 'TBL_PRIMES'; go </span> |
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.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Older system stored proc - list by object sp_columns 'TBL_PRIMES' go </span> |
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.
|
1 2 3 4 5 6 7 |
<span style="color: #008000; font-size: small;">-- My columns in my table select b.name, * from sys.columns a join sys.types b on a.system_type_id = b.system_type_id where a.object_id = OBJECT_ID('DBO.TBL_PRIMES') go </span> |
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.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Older system stored proc - all indexes sp_helpindex 'TBL_PRIMES' go </span> |
Like most examples today, there is a system catalog named sys.indexes view that can be queried to get the same information.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- My clustered index select * from sys.indexes where name like 'PK%' go </span> |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
<span style="color: #008000; font-size: small;">-- Remove table if it exists IF OBJECT_ID('[DBO].[TBL_TOP_500_PRIMES]') > 0 BEGIN DROP TABLE [DBO].[TBL_TOP_500_PRIMES] END -- Create table to hold subset CREATE TABLE [DBO].[TBL_TOP_500_PRIMES] ( [MY_ID] [int] identity (1, 1) NOT NULL, [MY_VALUE] [bigint] NOT NULL, [MY_DIVISION] [bigint] NOT NULL, [MY_SEARCH_PCT] AS (CAST([MY_DIVISION] AS REAL) / CAST([MY_VALUE] AS REAL) * 100.00), CONSTRAINT [PK_TBL_TOP_500_PRIMES] PRIMARY KEY CLUSTERED ( [MY_VALUE] ASC ) ) ON [PRIMARY] GO -- Add foreign key ALTER TABLE [DBO].[TBL_TOP_500_PRIMES] WITH CHECK ADD CONSTRAINT [FK_TBL_PRIMES] FOREIGN KEY([MY_VALUE]) REFERENCES [DBO].[TBL_PRIMES] ([MY_VALUE]) GO -- Load table with data INSERT INTO [DBO].[TBL_TOP_500_PRIMES] (MY_VALUE, MY_DIVISION) SELECT TOP 500 MY_VALUE, MY_DIVISION FROM [DBO].[TBL_PRIMES] ORDER BY MY_VALUE ASC GO -- Show the data in the table SELECT * FROM [DBO].[TBL_TOP_500_PRIMES] GO </span> |
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.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Show columns that are computed select * from sys.computed_columns go </span> |
A query against the system catalog named sys.identity_columns will show us the [MY_ID] column.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Show columns that are identity select * from sys.identity_columns where name like 'MY%' go </span> |
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.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Older system stored proc - just primary key sp_pkeys 'TBL_PRIMES' go </span> |
Again, there is a system catalog view named sys.key_constraints that can be queried to get information on primary or unique constraints.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Sub-view of sys.objects select * from sys.key_constraints go </span> |
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.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Sub-view of sys.objects select * from sys.check_constraints go </span> |
The sys.default_constraints view can be used to see what default constraints exist in the current database.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Sub-view of sys.objects select * from sys.default_constraints go </span> |
The sys.foreign_keys view can be used to see what foreign constraints exist in the current database.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Sub-view of sys.objects select * from sys.foreign_keys go </span> |
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.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- All my constraints select * from sys.objects where is_ms_shipped = 0 and type in ('C', 'D', 'UQ', 'PK', 'F') go </span> |
Last but not least, the sp_helpconstraint stored procedure can be used to return information on all constraints given a table name.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Returns a bunch of info. sp_helpconstraint @objname = 'TBL_PRIMES' go </span> |
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.
