Problem
I noticed multiple references to the sys.objects view at MSSQLTIps.com and other internet sites. Most of these references show examples of how to use object_id values from sys views for one or more purposes. However, I am looking for more examples and commentary that will equip me to tackle any kind of issue that sys.objects can address with basic T-SQL skills.
Solution
The sys.objects view is one type of SQL Server system view for finding metadata, which is data about the data in a database. Another approach to discovering metadata in SQL Server is with INFORMATION_SCHEMA views. Both the SQL Server system views and the more generic INFORMATION_SCHEMA views can be found in Views under System Views in Object Explorer for each database within an SQL Server instance.
The sys.objects view has one row for each schema-scoped object in each database on a SQL Server instance. Sys.objects is a preconfigured view that references an internal SQL Server table. The contents of each sys.objects view can vary from one database to the next depending on the objects residing in each database. Database administrators and developers are not allowed direct access to the internal SQL Server tables, but administrators and developers can examine the contents of the sys.objects view and related sys views, such as sys.tables and sys.columns.
The INFORMATION_SCHEMA views are generic in the sense that they reference a standard across multiple database vendors. To the extent that two different database vendors implement the standard corresponding way, queries using the INFORMATION_SCHEMA views function identically across vendors. SQL Server system views provide more detailed metadata than is available from INFORMATION_SCHEMA views. Additionally, you can use SQL code to combine two or more system views in stored procedures and user views, but this is not possible with INFORMATION_SCHEMA views. Finally, queries based on system views run more quickly in SQL Server than INFORMATION_SCHEMA views because SQL Server system views depend on internal system tables that are maintained in an SQL Server instance.
Here is a list of some prior MSSQLTips.com articles that you may care to review to grow your understanding of querying for metadata in SQL Server.
- Starter Metadata T-SQL Query Examples for Databases and Tables
- Query SQL Server system data about tables and columns
- Find SQL Server Stored Procedure Create, Modified, Last Execution Date and Code
- SQL Server INFORMATION_SCHEMA views Tutorial
- SQL Server System Objects in User Defined Databases
- List columns and attributes for every table in a SQL Server database
- Over 40 queries to find SQL Server tables with or without a certain property
A Quick Introduction to the Sys.objects View
An excellent place to begin learning about sys.objects would be the Microsoft.com site for its coverage of the sys.objects view. The learn.microsoft.com web page on sys.objects offers a review of all the columns returned by the view as well as a handful of query examples. Depending on your needs, it might be worth saving your reference to this page because it covers documentation that is not readily available from other sources.
The following query statement shows how to list the objects in an SQL Server database. Replace database_name with the name of the database for which you seek metadata.
select * from database_name.sys.objects
Instead of explicitly specifying the target database name for which you seek metadata, you can precede a select statement with a SQL Server Management Studio use statement to designate a default database for which you seek metadata. Here’s an example of the syntax for this approach.
-- prepared by Rick Dobson for MSSQLTips.com
use database_name
go
select * from sys.objects
Each row in the sys.objects view for a database has a unique object_id value. Object_id values are distinct within but not between different databases. All SQL Server objects in a database are denoted by an object_id value as well as a name value for the object and the schema to which it belongs. Additionally, there are two built-in SQL Server functions that can return the name as well as the object_id of a SQL Server object in a database.
- The object_id function returns an int datatype value for the object_id value of a SQL Server object given the object’s name as a string parameter value.
- The object_name function returns a sysname datatype value for the object_name value, which is functionally equivalent to a nvarchar(128) datatype that is not nullable; the string parameter value for the object_name is not restricted to a sysname datatype (for example, the string parameter value can have char, varchar, and nvarchar string values of lengths up to 128 characters).
The following script illustrates a way to invoke these two functions.
- The use statement at the top of the script designates the DataScience database, which is a sample database that I use for many of my MSSQLTips.com articles. You should replace it with a database name in your computing environment so long as it is populated with a reasonable number of SQL Server objects.
- Two local variables are specified with a declare statement
- @my_object_name has a varchar(50) datatype
- @my_object_id has an int datatype
- A set statement assigns a string value (‘dbo.symbol_date’) to @my_object_name.
- The next two select statements invoke the object_id function with two different parameter values
- The parameter value for the first object_id function is a string constant (‘dbo.symbol_date’)
- The parameter value for the second object_id function references a local variable (@my_object_name)
- The remainder of the script shows two different ways to invoke the object_name function
- This script section begins by computing the object_id value for the string value in @my_object_name; the computed object_id value is 1527728545 in the DataScience database on the SQL Server instance for this tip.
- Next, two select statements display both the object_id and object_name values for the SQL Server object with a name of dbo.symbol_date and an object_id value of 1527728545.
- As with the object_id function, the object_name function can accept a literal value or a local variable value.
-- prepared by Rick Dobson for MSSQLTips.com
use DataScience
go
declare
@my_object_name varchar(50)
,@my_object_id int
-- assign value to @my_object_name
set @my_object_name = 'dbo.symbol_date'
-- two ways to return an object_id
select object_id('dbo.symbol_date') [object_id from string constant]
select object_id(@my_object_name) [object_id from local variable];
-- assign value to @my_object_id
set @my_object_id = (select object_id(@my_object_name))
-- two ways to return an object_id
select
object_id('dbo.symbol_date') [object_id from string constant]
,object_name(1527728545) [object_name from int value]
select
object_id(@my_object_name) [object_id from local variable]
, object_name(@my_object_id) [object_name from local variable];
Here is a collection of screenshots with a results set for each of the select statements in the preceding script. The first two results sets are for object_id function values from a literal value and a local variable value. The second two results sets are for both object_id and object_name return values.
The next script introduces several additional columns from the sys.objects view and a new metadata function.
- The name, schema_id, type, type_desc, create_date, and modify_date are select list items from the sys.objects view
- The schema_id column value is numeric, but it points to a schema name
- The schema_name function translates the numeric schema_id value to a schema name, such as dbo
- The create_date and modify_date select list items are for dates from the sys.objects view
- The create_date is normally the date that a SQL Server object was created
- The modify_date is the date a SQL Server object is changed with an alter statement
- Notice that this script does not have a use statement to specify a default database. However, the script does have a database name followed by a period before the sys_objects view in the from clause
- A where clause concludes the script by the selection of just the row from sys.objects with an object_id value of 1527728545
-- prepared by Rick Dobson for MSSQLTips.com
-- getting the schema, type, create and modify dates of an object
select
name
,schema_id
,schema_name(schema_id)[schema name]
,type
,type_desc
,create_date
,modify_date
from DataScience.sys.objects where object_id = 1527728545
Here is a screenshot with the results set from the preceding script. This representation of the results shows the SQL Server object name in the name column without a preceding schema name. Instead, the schema name for the SQL Server object appears in a separate column from the database object name.
The modify_date is offset from the create_date by 13 milliseconds. This gap between the create_date and modify_date results from an alter table statement after the create table statement that adds a primary key constraint to the symbol_date table. The 13 milliseconds reflect the time that it takes to update the symbol_date table.
Querying by Type and Displaying Type and Type_desc Columns from Sys.objects
This section focuses on the type and type_desc columns from the sys.objects view for a database. There are as many as 30 or so SQL Server object types tracked by the sys.objects view. The number of tracked database object types increases whenever Microsoft adds a new type of database object with successive versions of SQL Server. The full list of tracked object types is available at the learn.microsoft.com web page on sys.objects.
The preceding section focuses on an SQL Server object in the DataScience database that has an object_id value of 1527728545. The screenshot at the end of the preceding section identifies the object as a user-created table with a type of U and a type_desc of USER_TABLE.
The script for this section focuses on the types of SQL Server objects in the DataScience database. This is just a database in which I created code for populating tables with data and implementing processes with SQL scripts, stored procedures, and scalar functions. It is likely that you have one or more databases like this on the SQL Server instances that you administer or on which you develop solutions.
The following script contains a SQL Server Management Studio (SSMS) use statement followed by two select statements. These select statements implement very basic operations for discovering the SQL Server object types in an SQL Server database.
- The use statement specifies DataScience as the default database for the script. When running this script in your environment, replace DataScience with the name about which you want to start discovering metadata
- The first select statement displays a results set with one row for each distinct data type in the default database specified by the use statement
- If you comment out the distinct keyword, then the select statement will return a results set with one row for each instance of a SQL Server object type in the default database
- If you comment out the distinct keyword, then you may also care to add object_id and name to the set of select list items. This will allow you to identify the separate instances of each object type
- The second select statement displays a subset of the distinct object types in the default database
- The where clause includes a type code for each distinct object type that you want in the results set
- The type codes in the second select statement are
- fn for SQL scalar function
- f for FOREIGN KEY constraint
- u for user-defined table
- pk for PRIMARY KEY constraint
- p for SQL stored procedure
-- prepared by Rick Dobson for MSSQLTips.com
use DataScience
go
-- show me the object type and type_desc values for objects in the default database
select
distinct
type
,type_desc
FROM sys.objects
--show me a subset of object types in the default sql server database
select
distinct
type
,type_desc
from sys.objects
where type in
('fn'
,'f'
,'u'
,'pk'
,'p'
)
Here are the two results sets from the preceding script on the SQL Server instance that I use for my contributions to MSSQLTips.com.
- The top results set shows the full set of distinct object types in the DataScience database as of the time this screen shot was taken. Notice that there eight distinct object types in the database
- When you are working with a database, you may not care to gather metadata about all the distinct object types in a database. The second results set shows just a subset of the distinct object types in the DataScience database
Querying and Displaying Parent-Child Relationships Among SQL Server Database Objects
The parent_object_id column in sys.objects and related sys views contains a different kind of content than the other columns in sys views. Sys views column values typically convey information about the current row of a sys view; parent_object_id column values point at the parent of current row. As a result, the parent_object_id can help you identify the presence or absence of parent-child relationships between metadata objects. For example, a primary key object is for a user table, but the primary key object is distinct from its matching table object.
- When a table object has a primary key, then the primary key object_id value for the table is in the parent_object_id column for the primary key object. This indicates that the primary key object is a child of the table object
- On the other hand, when a table does not have a primary key object, then there is no primary key object with a parent_object_id pointing to the table
There are four select statements in the example for this section.
- The first select statement extracts the object_id, table name, and schema name for rows from sys.tables for the DataScience database
- The second select statement returns rows based on sys.objects for primary key (pk) objects in the DataScience database
- The third select statement returns rows from sys.tables whose object_id values match a parent_object_id value for a sys.objects row pointing at a primary key object
- The fourth select statement returns a different set of rows from sys.tables whose object_id values do not match a parent_object_id value for a sys.objects row pointing at a primary key object
-- prepared by Rick Dobson for MSSQLTips.com
-- list all tables from DataScience.sys.tables by object_id
select
object_id
,name
,schema_name(schema_id) schema_name
from DataScience.sys.tables
order by name
-- list names for all primary keys in DataScience
select
object_id
,type_desc
,name
,parent_object_id
from DataScience.sys.objects
where type in ('pk')
-- list all table objects with a primary key in DataScience
select
object_id
,name
,schema_name(schema_id) schema_name
from DataScience.sys.tables
where object_id in
(
-- list parent_object_names for all primary keys in default database
select
parent_object_id
from DataScience.sys.objects
where type in ('pk')
)
-- list all tables without a primary key in DataScience
select
object_id
,name
,schema_name(schema_id) schema_name
from DataScience.sys.tables
where object_id not in
(
-- list parent_object_names for all primary keys in default database
select
parent_object_id
from DataScience.sys.objects
where type in ('pk')
)
Here are excerpts from the four results sets from the preceding script – one for each of its select statements. Each excerpt shows the first eight rows from its results set.
- The first results set is for rows from sys.tables sorted by name column values. As of the time this screenshot was made, there were 40 rows in sys.tables
- The second results set is for rows from sys.objects whose type value is pk. As of the time this screenshot was made, there were 10 sys.objects rows pointing at primary key objects
- The third results set is for rows from sys.tables whose object_id column values match one of the parent_object_id values from the second results set. The number of rows in this results set must match the number of rows in the second results set. A more important contribution of the results set is that it shows the names for tables with a primary key in the DataScience database
- The fourth results set is for rows from sys.tables whose object_id column values do not match any of the parent_object_id values from the second results set. The number of rows in this results set must match the number of rows in the first results set less the number of rows in the second results set. The main role of the results set is that it shows the table names without a primary key in the DataScience database
Next Steps
Sys views are an immensely powerful tool for enabling metadata discovery projects. This kind of project can be particularly valuable when you need to discover the structure of a database that you did not personally build. This tip focuses on the sys.objects view and some introductory use cases for sys views.
Sys views are a rich topic with many possible uses for SQL Server databases. There are seven references at the end of the Solution section for this tip that highlight additional code samples for empowering your SQL Server metadata discovery projects. Also, do not forget to examine the learn.microsoft.com web page on sys.objects.
Finally, you should be aware that there are two schools of thought about when and how to use INFORMATION_SCHEMA views versus sys views in SQL Server metadata discovery projects. Here are some additional references specifically on these contrasting methodologies:
