Query SQL Server sys.objects for Database Objects Information (2024)

By: Rick Dobson |Updated: 2023-12-20 |Comments | Related: More > TSQL

Problem

I noticed multiple references to the sys.objects view at MSSQLTIps.com and otherinternet sites. Most of these references show examples of how to use object_idvalues from sys views for one or more purposes. However, I am looking formore examples and commentary that will equip me to tackle any kind of issue thatsys.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 discoveringmetadata in SQL Server is with INFORMATION_SCHEMA views. Both the SQL Serversystem views and the more generic INFORMATION_SCHEMA views can be found in Viewsunder 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 databaseon a SQL Server instance. Sys.objects is a preconfigured view that referencesan internal SQL Server table. The contents of each sys.objects view can varyfrom one database to the next depending on the objects residing in each database.Database administrators and developers are not allowed direct access to the internalSQL Server tables, but administrators and developers can examine the contents ofthe 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 standardacross multiple database vendors. To the extent that two different databasevendors implement the standard corresponding way, queries using the INFORMATION_SCHEMAviews function identically across vendors. SQL Server system views providemore 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 anduser views, but this is not possible with INFORMATION_SCHEMA views. Finally,queries based on system views run more quickly in SQL Server than INFORMATION_SCHEMAviews because SQL Server system views depend on internal system tables that aremaintained in an SQL Server instance.

Here is a list of some prior MSSQLTips.com articles that you may care to reviewto 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 beginlearning about sys.objects would be the Microsoft.com site for its coverage of thesys.objects view.Thelearn.microsoft.com web page on sys.objectsoffers a review of all the columns returned by the view as well as a handful ofquery examples.Depending on your needs,it might be worth saving your reference to this page because it covers documentationthat is not readily available from other sources.

The following query statementshows how to list the objects in an SQL Server database.Replace database_name withthe name of the database for which you seek metadata.

select * from database_name.sys.objects

Instead of explicitly specifyingthe target database name for which you seek metadata, you can precede a select statementwith a SQL Server Management Studio use statement to designate a default databasefor which you seek metadata.Here’s an exampleof the syntax for this approach.

-- prepared by Rick Dobson for MSSQLTips.com use database_namegoselect * from sys.objects

Each row in the sys.objectsview for a database has a unique object_id value.Object_id values are distinctwithin but not between different databases.All SQL Server objects ina database are denoted by an object_id value as well as a name value for the objectand the schema to which it belongs.Additionally, there aretwo built-in SQL Server functions that can return the name as well as the object_idof a SQL Server object in a database.

• Theobject_id function returns an int datatype value for the object_id value ofa SQL Server object given the object’s name as a string parameter value.
• Theobject_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 stringparameter value for the object_name is not restricted to a sysname datatype(for example, the string parameter value can have char, varchar, and nvarcharstring values of lengths up to 128 characters).

The following script illustratesa way to invoke these two functions.

• Theuse 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 shouldreplace it with a database name in your computing environment so long as itis populated with a reasonable number of SQL Server objects.
• Twolocal variables are specified with a declare statement
  • @my_object_namehas a varchar(50) datatype
  • @my_object_idhas an int datatype
• Aset statement assigns a string value (‘dbo.symbol_date’) to @my_object_name.
• Thenext two select statements invoke the object_id function with two differentparameter values
  • Theparameter value for the first object_id function is a string constant (‘dbo.symbol_date’)
  • Theparameter value for the second object_id function references a local variable(@my_object_name)
• Theremainder of the script shows two different ways to invoke the object_name function
  • Thisscript section begins by computing the object_id value for the string valuein @my_object_name; the computed object_id value is 1527728545 in the DataScience databaseon the SQL Server instance for this tip.
  • Next,two select statements display both the object_id and object_name valuesfor the SQL Server object with a name of dbo.symbol_date and an object_idvalue of 1527728545.
  • Aswith the object_id function, the object_name function can accept a literalvalue or a local variable value.

-- prepared by Rick Dobson for MSSQLTips.com use DataSciencego declare @my_object_name varchar(50),@my_object_id int -- assign value to @my_object_nameset @my_object_name = 'dbo.symbol_date' -- two ways to return an object_idselect 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_idset @my_object_id = (select object_id(@my_object_name)) -- two ways to return an object_idselect 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 screenshotswith a results set for each of the select statements in the preceding script.The first two results setsare for object_id function values from a literal value and a local variable value.The second two results setsare for both object_id and object_name return values.

The next script introduces severaladditional columns from the sys.objects view and a new metadata function.

• Thename, schema_id, type, type_desc, create_date, and modify_date are select listitems from the sys.objects view
• Theschema_id column value is numeric, but it points to a schema name
• Theschema_name function translates the numeric schema_id value to a schema name,such as dbo
• Thecreate_date and modify_date select list items are for dates from the sys.objectsview
  • Thecreate_date is normally the date that a SQL Server object was created
  • Themodify_date is the date a SQL Server object is changed with an alter statement
• Noticethat 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_objectsview in the from clause
• Awhere clause concludes the script by the selection of just the row from sys.objectswith an object_id value of 1527728545

-- prepared by Rick Dobson for MSSQLTips.com -- getting the schema, type, create and modify dates of an objectselect name,schema_id ,schema_name(schema_id)[schema name],type,type_desc,create_date,modify_datefrom DataScience.sys.objects where object_id = 1527728545

Here is a screenshot with theresults set from the preceding script.This representation of theresults shows the SQL Server object name in the name column without a precedingschema name.Instead, the schema namefor the SQL Server object appears in a separate column from the database objectname.

The modify_date is offset fromthe create_date by 13 milliseconds.This gap between the create_dateand modify_date results from an alter table statement after the create table statementthat adds a primary key constraint to the symbol_date table.The 13 milliseconds reflectthe 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 viewfor a database. There are as many as 30 or so SQL Server object types trackedby the sys.objects view. The number of tracked database object types increaseswhenever Microsoft adds a new type of database object with successive versions ofSQL Server. The full list of tracked object types is available atthelearn.microsoft.com web page on sys.objects.

The preceding section focuses on an SQL Server object in the DataScience databasethat has an object_id value of 1527728545. The screenshot at the end of thepreceding section identifies the object as a user-created table with a type of Uand a type_desc of USER_TABLE.

The script for this section focuses on the types of SQL Server objects in theDataScience database. This is just a database in which I created code forpopulating tables with data and implementing processes with SQL scripts, storedprocedures, and scalar functions. It is likely that you have one or more databaseslike this on the SQL Server instances that you administer or on which you developsolutions.

The following script contains a SQL Server Management Studio (SSMS) use statementfollowed by two select statements. These select statements implement verybasic operations for discovering the SQL Server object types in an SQL Server database.

• The use statement specifies DataScience as the default database for thescript. When running this script in your environment, replace DataSciencewith the name about which you want to start discovering metadata
• The first select statement displays a results set with one row for eachdistinct data type in the default database specified by the use statement
  • If you comment out the distinct keyword, then the select statement willreturn a results set with one row for each instance of a SQL Server objecttype in the default database
  • If you comment out the distinct keyword, then you may also care to addobject_id and name to the set of select list items. This will allowyou to identify the separate instances of each object type
• The second select statement displays a subset of the distinct object typesin the default database
  • The where clause includes a type code for each distinct object typethat 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 DataSciencego -- show me the object type and type_desc values for objects in the default databaseselect distinct type,type_descFROM sys.objects --show me a subset of object types in the default sql server databaseselect distinct type,type_descfrom sys.objectswhere type in ('fn','f','u','pk','p')

Here are the two results sets from the preceding script on the SQL Server instancethat I use for my contributions to MSSQLTips.com.

• The top results set shows the full set of distinct object types in the DataSciencedatabase as of the time this screen shot was taken. Notice that thereeight distinct object types in the database
• When you are working with a database, you may not care to gather metadataabout all the distinct object types in a database. The second resultsset shows just a subset of the distinct object types in the DataScience database

Querying and Displaying Parent-Child Relationships Among SQL Server DatabaseObjects

The parent_object_id column in sys.objects and related sys views contains a differentkind of content than the other columns in sys views. Sys views column valuestypically convey information about the current row of a sys view; parent_object_idcolumn values point at the parent of current row. As a result, the parent_object_idcan help you identify the presence or absence of parent-child relationships betweenmetadata objects. For example, a primary key object is for a user table, butthe primary key object is distinct from its matching table object.

• When a table object has a primary key, then the primary key object_id valuefor 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, thenthere 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 schemaname for rows from sys.tables for the DataScience database
• The second select statement returns rows based on sys.objects for primarykey (pk) objects in the DataScience database
• The third select statement returns rows from sys.tables whose object_idvalues match a parent_object_id value for a sys.objects row pointing at a primarykey object
• The fourth select statement returns a different set of rows from sys.tableswhose object_id values do not match a parent_object_id value for a sys.objectsrow pointing at a primary key object

-- prepared by Rick Dobson for MSSQLTips.com -- list all tables from DataScience.sys.tables by object_idselect object_id,name,schema_name(schema_id) schema_namefrom DataScience.sys.tablesorder by name -- list names for all primary keys in DataScienceselect object_id,type_desc,name,parent_object_idfrom DataScience.sys.objectswhere type in ('pk') -- list all table objects with a primary key in DataScienceselect object_id,name,schema_name(schema_id) schema_namefrom DataScience.sys.tableswhere object_id in(-- list parent_object_names for all primary keys in default databaseselect parent_object_idfrom DataScience.sys.objectswhere type in ('pk')) -- list all tables without a primary key in DataScienceselect object_id,name,schema_name(schema_id) schema_namefrom DataScience.sys.tableswhere object_id not in(-- list parent_object_names for all primary keys in default databaseselect parent_object_idfrom DataScience.sys.objectswhere 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 eightrows from its results set.

• The first results set is for rows from sys.tables sorted by name columnvalues. As of the time this screenshot was made, there were 40 rows insys.tables
• The second results set is for rows from sys.objects whose type value ispk. As of the time this screenshot was made, there were 10 sys.objectsrows pointing at primary key objects
• The third results set is for rows from sys.tables whose object_id columnvalues 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 thesecond results set. A more important contribution of the results set isthat 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 columnvalues do not match any of the parent_object_id values from the second resultsset. The number of rows in this results set must match the number of rowsin 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 aprimary 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 thestructure of a database that you did not personally build. This tip focuseson 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 thathighlight additional code samples for empowering your SQL Server metadata discoveryprojects. Also, do not forget to examinethelearn.microsoft.com web page on sys.objects.

Finally, you should be aware that there are two schools of thought about whenand how to use INFORMATION_SCHEMA views versus sys views in SQL Server metadatadiscovery projects. Here are some additional references specifically on thesecontrasting methodologies:

• The case against INFORMATION_SCHEMA views
• Difference between INFORMATION_SCHEMA and SYS schema in SQL SERVER
• Difference between Information_schema vs sys tables in SQL Server

About the author

Article Last Updated: 2023-12-20