DataBaseZone.com
Conrad Muller
Seattle, Washington

Email: conrad at
databasezone
dot com

How scripts retrieve information about our database objects


Introduction:

The data we store in a database is not stored in the form of tables, columns, and rows. It is stored in a very long string of data which is broken up into equal sized blocks. The blocks are numbered so that the computer can find them and return the contents.

We will consider the tables, columns, indexes, constraints, and other components of our database to be objects. From here on I will call them database objects, or just objects.

The computer manipulates the information into tables and rows to display it to us. The stored instructions that allow the transformation of a long sequence of data into database objects (tables and rows) are stored in System Tables. These stored instructions are called Metadata and are often referred to as "data about data".

The metadata also includes information that the computer needs to work with the information, such as the type of data in a column.

How data about system objects is stored:

Very briefly, a database application stores both our data and the data required to make sense of our data. Our data is stored in User Tables, the metadata is stored in System Tables.

A few of the System Tables are:

How we can access data about system objects:

We can return the information about our metadata by querying the system tables, but system tables can change between versions of SQL Server. This would break our code. To give us access to the system data without directly accessing the system tables, Microsoft has given us System Views.

Views are SQL code that can combine data from multiple tables and present it as if it was a single table. System views are provided for us so that if system tables change, Microsoft only needs to change the views that access them. The output of the views stays the same between versions, even if the underlying tables change.

You can look at the ouput of the system views in SSMS Databases/YourDatabase/Views/System Views. Right click on a system view and choose Select top 100 rows.

Some system views are:

Notice that all of the System Views are located in the sys schema. There are 381 system views in the sys schema in my copy of SQL Server 2012.

How we use system views to retrieve Metadata from a database

When you use SQL Server Management Studio (SSMS) to execute:

USE CalendarHoliday -- execute the script against the CalendarHoliday database
GO

SELECT * FROM sys.objects
GO

against the CalendarHoliday database, you get the following in part:

name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published
CreatedByAccountID_AccountAccountRole_FK1 1988202133 NULL 1 1861581670 F FOREIGN_KEY_CONSTRAINT 5/24/2013 6/9/2013 0 0 0
Calendar 1989582126 NULL 1 0 U USER_TABLE 5/24/2013 6/9/2013 0 0 0
queue_messages_1977058079 1993058136 NULL 4 1977058079 IT INTERNAL_TABLE 5/24/2013 6/9/2013 1 0 0
ModifiedByAccountID_AccountAccountRole_FK1 2004202190 NULL 1 1861581670 F FOREIGN_KEY_CONSTRAINT 5/24/2013 6/9/2013 0 0 0
PK_Calendar_1 2005582183 NULL 1 1989582126 PK PRIMARY_KEY_CONSTRAINT 5/24/2013 5/24/2013 0 0 0
EventNotificationErrorsQueue 2009058193 NULL 1 0 SQ SERVICE_QUEUE 5/26/2013 6/9/2013 1 0 0
CreatedByAccountID_AccountRole_FK1 2020202247 NULL 1 1893581784 F FOREIGN_KEY_CONSTRAINT 5/24/2013 6/9/2013 0 0 0
queue_messages_2009058193 2025058250 NULL 4 2009058193 IT INTERNAL_TABLE 5/26/2013 6/9/2013 1 0 0

If we are only interested in User Tables, we add a WHERE clause to the query:

USE CalendarHoliday
GO

SELECT * FROM sys.objects
WHERE type = 'U'

GO

This gives us a list that includes only User Tables:

name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published
HolidayCategory 2099048 NULL 1 0 U USER_TABLE 5/24/2013 6/9/2013 0 0 0
HolidayFixed 34099162 NULL 1 0 U USER_TABLE 5/24/2013 6/9/2013 0 0 0
HolidayMovable 66099276 NULL 1 0 U USER_TABLE 5/24/2013 6/9/2013 0 0 0
HolidayType 98099390 NULL 1 0 U USER_TABLE 5/24/2013 6/9/2013 0 0 0
LoginAttempt 130099504 NULL 1 0 U USER_TABLE 5/24/2013 6/9/2013 0 0 0
CalendarHolidayFixed 199671759 NULL 1 0 U USER_TABLE 5/24/2013 6/9/2013 0 0 0
Password 290100074 NULL 1 0 U USER_TABLE 5/24/2013 6/9/2013 0 0 0
PersonName 375672386 NULL 1 0 U USER_TABLE 5/24/2013 6/9/2013 0 0 0

 

Let's look at how the scripts (queries) in the article build on this

This a sample of the output of ShowTableColumnDescriptions.sql:

Schema TableName ColumnName ColumnOrder DataType Collation IsNullable ANSIPadded ColumnDescription
dbo Account BeginDate 4 datetime   Nullable   Date and time the Account becomes active.
dbo Account EndDate 5 datetime   Nullable   Date and time the Account access ends.
dbo Account Active 6 bit       If true, this entry is still active.
dbo Account CreatedByAccountID 7 uniqueidentifier       The PersonOrganizationRoleID which was logged on during creation of this entry.
dbo Account CreatedDate 8 datetime       Date and time the entry was created.
dbo Account ModifiedByAccountID 9 uniqueidentifier       The ID of the Account which was logged on when this entry was last modified.
dbo Account ModifiedDate 10 datetime       Date and time the entry was last modified.

It takes five tables to return all of this data, and here they are:

Entity Relationship Diagram of some sys tables

The checks in the checkboxes show which columns are returned.

The lines between tables show the columns that are used to relate (connect) the data in such a way that the information for each Database Object is returned in one row. This type of diagram is called an ERD (Entity Relationship Diagram). Of course there are many types of ERD Diagram, but this one is provided by the Query Editor in SQL Server Management Studio.

The minimal TSQL code looks like this:

SELECT sys.schemas.name, sys.tables.name, sys.columns.name,
    sys.columns.column_id, sys.types.name, sys.columns.collation_name,
    sys.columns.is_nullable, sys.columns.is_ansi_padded, sys.extended_properties.value

FROM sys.tables LEFT OUTER JOIN
    sys.columns ON sys.tables.object_id = sys.columns.object_id LEFT OUTER JOIN
    sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id LEFT OUTER JOIN
    sys.extended_properties ON sys.extended_properties.major_id = sys.columns.object_id AND
    sys.extended_properties.minor_id = sys.columns.column_id LEFT OUTER JOIN
    sys.types ON sys.types.system_type_id = sys.columns.system_type_id

WHERE sys.types.name <> 'sysname'

ORDER BY sys.schemas.name, sys.tables.name, sys.columns.column_id

The SELECT clause lists the data items that we want to return. This is the same as the checked columns in the table diagram.

The FROM clause specifies all five of the tables and how they are related. You can compare this against the diagram.

The WHERE clause filters out a few extraneous entries.

The ORDER BY clause puts the rows in order by schema name, table name, and column_id (Column Order).

The full ShowTableColumnDescriptions.sql is available for you to download, view, and execute. Most of the enhancements to the full version give the output of the query more useful column names, or show the details of each column's data type.


This page is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 2.5 License by Conrad Muller.

The scripts themselves are intended for education and are provided "AS IS"without warranty of any kind, either expressed or implied, including but not limited to implied warranties of merchantability and/or fitness for a particular purpose.

Home | Resume | Project Portfolio | Writings | Developer Resources | Contact Form