SQL server

System catalog

Three useful system catalog views for finding object information:

  • sys.tables
  • sys.all_columns
  • sys.types

sys.tables provides one row for each table in a database.  This does include user tables and system tables that exist in each database.  There is a column within sys.tables: [is_ms_shipped] that identifies system tables.  This allows you to filter your results to just user tables if you so desire without having to join to the sys.sysobject system compatibility view’s type column (where sys.sysobjects.type = ‘U’) in order to do so.

sys.all_columns offers a row for each column for every object in a database.  Many of the columns are shared with sys.types, and we pull most of the metadata from this view, but there is still one column lacking from our result set that we must pull from sys.types.

sys.types is the catalog view that stores rows relating to system and user-defined data types and their properties.  The only field from this view we will need is the data type’s name, as it is the only field in our desired result set we can’t return from sys.all_columns as it pertains to column metadata.

Each of the catalog views are scoped at the database level, meaning that they exist in each database (system or user) on the SQL Server instance.  Even sys.types, a catalog view you may expect to be the same across all databases on an instance is scoped at the database level.  Why?  Quite simply put, sys.types includes user-defined datatypes that are unique to a database.  Database collation also has an impact on sys.types, therefore making native data types such as text, ntext, varchar(), char(), nvarchar(), nchar(), and sysname different between databases if their collations differ.


Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s