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:
- sysobjects
- sysforeignkeys
- sysdevices
- syslanguages
- sysconstraints
- sysmessages
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:
- sys.objects
- sys.foreign_keys
- sys.indexes
- sys.partitions
- sys.allocation_units
- sys.dm_db_partition_stats
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:
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.