sys.objects

sys.objects#

Returns one row for every schema-scoped object in the current virtual database: tables, views, table-valued functions, primary keys, indexes, foreign keys and check constraints. The view has the columns of SQL Server’s sys.objects; sys.all_objects, sys.tables, sys.views and sys.all_views report the same values for the same object, and the compatibility view sysobjects reports create_date as both crdate and refdate.

The two date columns are the part of the surface that behaves differently from a clock, so they are documented in detail here.

Column name

Data type

Description

name

sysname

Object name.

object_id

int

Object identification number, unique within the database.

principal_id

int

Always NULL; objects are owned by their schema.

schema_id

int

Id of the schema the object belongs to.

parent_object_id

int

Id of the table a key, index, foreign key or check constraint belongs to; 0 for a table or view.

type

char(2)

Object type code (U table, V view, PK primary key, F foreign key, C check constraint, S system table, …).

type_desc

nvarchar(60)

Description of the object type.

create_date

datetime

When the object was created in Querona (see below).

modify_date

datetime

When the object’s definition last changed (see below).

is_ms_shipped

bit

1 for the system objects Querona ships, 0 for user objects.

is_published

bit

Always 0.

is_schema_published

bit

Always 0.

create_date and modify_date#

Both columns come from the timestamps Querona keeps for every table and view in its metadata store, presented in the server’s clock — the one GETDATE() returns — so they compare with it the way SQL Server’s do:

SELECT name, type_desc, modify_date
FROM sys.objects
WHERE modify_date > DATEADD(day, -7, GETDATE())
ORDER BY modify_date DESC;

create_date is set when the object is created and never moves. modify_date moves when the object’s definition changes: ALTER VIEW, adding, dropping or altering a column, renaming the object, changing its description or owner, and adding or dropping an index or foreign key on it. It does not move when statistics are refreshed (qua_update_object_statistics, UPDATE STATISTICS), when a materialized copy is loaded or reset, or when permissions or extended properties change — the same rule SQL Server applies.

Three cases differ from SQL Server:

  • A primary key, index, foreign key or check constraint reports the dates of the table it belongs to, because Querona keeps timestamps per table.

  • Adding a foreign key moves modify_date on the table that gained the constraint only; SQL Server also moves it on the referenced table.

  • The system objects (sys, INFORMATION_SCHEMA and the compatibility views) report the product build date, which changes on every upgrade; SQL Server reports the instance’s install time.

Objects created before the installation started keeping timestamps show a create_date that means “created no later than” the day the timestamps were introduced; modify_date becomes exact at the first definitional change after the upgrade.

Example#

SELECT SCHEMA_NAME(schema_id) AS schema_name, name, type_desc, create_date, modify_date
FROM sys.objects
WHERE type IN ('U', 'V')
ORDER BY schema_name, name;

See Also#