Bagikan melalui

  • Artikel

OBJECTPROPERTY (Transact-SQL)

Returns information about schema-scoped objects in the current database. For a list of schema-scoped objects, see sys.objects (Transact-SQL). This function cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications.

Topic link iconTransact-SQL Syntax Conventions

Syntax

OBJECTPROPERTY (id,property )

Arguments

  • id
    Is an expression that represents the ID of the object in the current database. id is int and is assumed to be a schema-scoped object in the current database context.

  • property
    Is an expression that represents the information to be returned for the object specified by id. property can be one of the following values.

    Note

    Unless noted otherwise, NULL is returned when property is not a valid property name, id is not a valid object ID, id is an unsupported object type for the specified property, or the caller does not have permission to view the object's metadata.

    Property name

    Object type

    Description and values returned

    CnstIsClustKey

    Constraint

    PRIMARY KEY constraint with a clustered index.

    1 = True

    0 = False

    CnstIsColumn

    Constraint

    CHECK, DEFAULT, or FOREIGN KEY constraint on a single column.

    1 = True

    0 = False

    CnstIsDeleteCascade

    Constraint

    FOREIGN KEY constraint with the ON DELETE CASCADE option.

    1 = True

    0 = False

    CnstIsDisabled

    Constraint

    Disabled constraint.

    1 = True

    0 = False

    CnstIsNonclustKey

    Constraint

    PRIMARY KEY or UNIQUE constraint with a nonclustered index.

    1 = True

    0 = False

    CnstIsNotRepl

    Constraint

    Constraint is defined by using the NOT FOR REPLICATION keywords.

    1 = True

    0 = False

    CnstIsNotTrusted

    Constraint

    Constraint was enabled without checking existing rows; therefore, the constraint may not hold for all rows.

    1 = True

    0 = False

    CnstIsUpdateCascade

    Constraint

    FOREIGN KEY constraint with the ON UPDATE CASCADE option.

    1 = True

    0 = False

    ExecIsAfterTrigger

    Trigger

    AFTER trigger.

    1 = True

    0 = False

    ExecIsAnsiNullsOn

    Transact-SQL function, Transact-SQL procedure, Transact-SQL trigger, view

    Setting of ANSI_NULLS at creation time.

    1 = True

    0 = False

    ExecIsDeleteTrigger

    Trigger

    DELETE trigger.

    1 = True

    0 = False

    ExecIsFirstDeleteTrigger

    Trigger

    First trigger fired when a DELETE is executed against the table.

    1 = True

    0 = False

    ExecIsFirstInsertTrigger

    Trigger

    First trigger fired when an INSERT is executed against the table.

    1 = True

    0 = False

    ExecIsFirstUpdateTrigger

    Trigger

    First trigger fired when an UPDATE is executed against the table.

    1 = True

    0 = False

    ExecIsInsertTrigger

    Trigger

    INSERT trigger.

    1 = True

    0 = False

    ExecIsInsteadOfTrigger

    Trigger

    INSTEAD OF trigger.

    1 = True

    0 = False

    ExecIsLastDeleteTrigger

    Trigger

    Last trigger fired when a DELETE is executed against the table.

    1 = True

    0 = False

    ExecIsLastInsertTrigger

    Trigger

    Last trigger fired when an INSERT is executed against the table.

    1 = True

    0 = False

    ExecIsLastUpdateTrigger

    Trigger

    Last trigger fired when an UPDATE is executed against the table.

    1 = True

    0 = False

    ExecIsQuotedIdentOn

    Transact-SQL function, Transact-SQL procedure, Transact-SQL trigger, view

    Setting of QUOTED_IDENTIFIER at creation time.

    1 = True

    0 = False

    ExecIsStartup

    Procedure

    Startup procedure.

    1 = True

    0 = False

    ExecIsTriggerDisabled

    Trigger

    Disabled trigger.

    1 = True

    0 = False

    ExecIsTriggerNotForRepl

    Trigger

    Trigger defined as NOT FOR REPLICATION.

    1 = True

    0 = False

    ExecIsUpdateTrigger

    Trigger

    UPDATE trigger.

    1 = True

    0 = False

    HasAfterTrigger

    Table, view

    Table or view has an AFTER trigger.

    1 = True

    0 = False

    HasDeleteTrigger

    Table, view

    Table or view has a DELETE trigger.

    1 = True

    0 = False

    HasInsertTrigger

    Table, view

    Table or view has an INSERT trigger.

    1 = True

    0 = False

    HasInsteadOfTrigger

    Table, view

    Table or view has an INSTEAD OF trigger.

    1 = True

    0 = False

    HasUpdateTrigger

    Table, view

    Table or view has an UPDATE trigger.

    1 = True

    0 = False

    IsAnsiNullsOn

    Transact-SQL function, Transact-SQL procedure, table, Transact-SQL trigger, view

    Specifies that the ANSI NULLS option setting for the table is ON. This means all comparisons against a null value evaluate to UNKNOWN. This setting applies to all expressions in the table definition, including computed columns and constraints, for as long as the table exists.

    1 = True

    0 = False

    IsCheckCnst

    Any schema-scoped object

    CHECK constraint.

    1 = True

    0 = False

    IsConstraint

    Any schema-scoped object

    Is a single column CHECK, DEFAULT, or FOREIGN KEY constraint on a column or table.

    1 = True

    0 = False

    IsDefault

    Any schema-scoped object

    Bound default.

    1 = True

    0 = False

    IsDefaultCnst

    Any schema-scoped object

    DEFAULT constraint.

    1 = True

    0 = False

    IsDeterministic

    Function, view

    The determinism property of the function or view.

    1 = Deterministic

    0 = Not Deterministic

    IsEncrypted

    Transact-SQL function, Transact-SQL procedure, table, Transact-SQL trigger, view

    Indicates that the original text of the module statement was converted to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server 2005. Users without access to system tables or database files cannot retrieve the obfuscated text. However, the text is available to users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the original procedure from memory at run time.

    1 = Encrypted

    0 = Not encrypted

    Base data type: int

    IsExecuted

    Any schema-scoped object

    Object can be executed (view, procedure, function, or trigger).

    1 = True

    0 = False

    IsExtendedProc

    Any schema-scoped object

    Extended procedure.

    1 = True

    0 = False

    IsForeignKey

    Any schema-scoped object

    FOREIGN KEY constraint.

    1 = True

    0 = False

    IsIndexed

    Table, view

    Table or view that has an index.

    1 = True

    0 = False

    IsIndexable

    Table, view

    Table or view on which an index can be created.

    1 = True

    0 = False

    IsInlineFunction

    Function

    Inline function.

    1 = Inline function

    0 = Not inline function

    IsMSShipped

    Any schema-scoped object

    Object created during installation of SQL Server.

    1 = True

    0 = False

    IsPrimaryKey

    Any schema-scoped object

    PRIMARY KEY constraint.

    1 = True

    0 = False

    NULL = Not a function, or object ID is not valid.

    IsProcedure

    Any schema-scoped object

    Procedure.

    1 = True

    0 = False

    IsQuotedIdentOn

    Transact-SQL function, Transact-SQL procedure, table, Transact-SQL trigger, view, CHECK constraint, DEFAULT definition

    Specifies that the quoted identifier setting for the object is ON. This means double quotation marks delimit identifiers in all expressions involved in the object definition.

    1 = ON

    0 = OFF

    IsQueue

    Any schema-scoped object

    Service Broker Queue

    1 = True

    0 = False

    IsReplProc

    Any schema-scoped object

    Replication procedure.

    1 = True

    0 = False

    IsRule

    Any schema-scoped object

    Bound rule.

    1 = True

    0 = False

    IsScalarFunction

    Function

    Scalar-valued function.

    1 = Scalar-valued function

    0 = Not scalar-valued function

    IsSchemaBound

    Function, view

    A schema bound function or view created by using SCHEMABINDING.

    1 = Schema-bound

    0 = Not schema-bound.

    IsSystemTable

    Table

    System table.

    1 = True

    0 = False

    IsTable

    Table

    Table.

    1 = True

    0 = False

    IsTableFunction

    Function

    Table-valued function.

    1 = Table-valued function

    0 = Not table-valued function

    IsTrigger

    Any schema-scoped object

    Trigger.

    1 = True

    0 = False

    IsUniqueCnst

    Any schema-scoped object

    UNIQUE constraint.

    1 = True

    0 = False

    IsUserTable

    Table

    User-defined table.

    1 = True

    0 = False

    IsView

    View

    View.

    1 = True

    0 = False

    OwnerId

    Any schema-scoped object

    Owner of the object.

    NoteNote
    The schema owner is not necessarily the object owner. For example, child objects (those where parent_object_id is nonnull) will always return the same owner ID as the parent.

    Nonnull = The database user ID of the object owner.

    TableDeleteTrigger

    Table

    Table has a DELETE trigger.

    >1 = ID of first trigger with the specified type.

    TableDeleteTriggerCount

    Table

    Table has the specified number of DELETE triggers.

    >0 = The number of DELETE triggers.

    TableFullTextMergeStatus

    Table

    Whether a table that has a full-text index that is currently in merging.

    0 = Table does not have a full-text index, or the full-text index is not in merging.

    1 = The full-text index is in merging.

    TableFullTextBackgroundUpdateIndexOn

    Table

    Table has full-text background update index (autochange tracking) enabled.

    1 = TRUE

    0 = FALSE

    TableFulltextCatalogId

    Table

    ID of the full-text catalog in which the full-text index data for the table resides.

    Nonzero = Full-text catalog ID, associated with the unique index that identifies the rows in a full-text indexed table.

    0 = Table does not have a full-text index.

    TableFulltextChangeTrackingOn

    Table

    Table has full-text change-tracking enabled.

    1 = TRUE

    0 = FALSE

    TableFulltextDocsProcessed

    Table

    Number of rows processed since the start of full-text indexing. In a table that is being indexed for full-text search, all the columns of one row are considered as part of one document to be indexed.

    0 = No active crawl or full-text indexing is completed.

    > 0 = One of the following:

    • The number of documents processed by insert or update operations since the start of Full, Incremental, or Manual change tracking population.

    • The number of rows processed by insert or update operations since change tracking with background update index population was enabled, the full-text index schema changed, the full-text catalog rebuilt, or the instance of SQL Server restarted, and so on.

    NULL = Table does not have a full-text index.

    NoteNote
    This property does not monitor or count deleted rows.

    TableFulltextFailCount

    Table

    Number of rows Full-Text Search did not index.

    0 = The population has completed.

    > 0 = One of the following:

    • The number of documents that were not indexed since the start of Full, Incremental, and Manual Update change tracking population.

    • For change tracking with background update index, the number of rows that were not indexed since the start of the population, or the restart of the population. This could be caused by a schema change, rebuild of the catalog, server restart, and so on.

    NULL = Table does not have a full-text index.

    TableFulltextItemCount

    Table

    Number of rows that were successfully full-text indexed.

    TableFulltextKeyColumn

    Table

    ID of the column associated with the single-column unique index that is participating in the full-text index definition.

    0 = Table does not have a full-text index.

    TableFulltextPendingChanges

    Table

    Number of pending change tracking entries to process.

    0 = change tracking is not enabled.

    NULL = Table does not have a full-text index.

    TableFulltextPopulateStatus

    Table

    0 = Idle.

    1 = Full population is in progress.

    2 = Incremental population is in progress.

    3 = Propagation of tracked changes is in progress.

    4 = Background update index is in progress, such as autochange tracking.

    5 = Full-text indexing is throttled or paused.

    TableHasActiveFulltextIndex

    Table

    Table has an active full-text index.

    1 = True

    0 = False

    TableHasCheckCnst

    Table

    Table has a CHECK constraint.

    1 = True

    0 = False

    TableHasClustIndex

    Table

    Table has a clustered index.

    1 = True

    0 = False

    TableHasDefaultCnst

    Table

    Table has a DEFAULT constraint.

    1 = True

    0 = False

    TableHasDeleteTrigger

    Table

    Table has a DELETE trigger.

    1 = True

    0 = False

    TableHasForeignKey

    Table

    Table has a FOREIGN KEY constraint.

    1 = True

    0 = False

    TableHasForeignRef

    Table

    Table is referenced by a FOREIGN KEY constraint.

    1 = True

    0 = False

    TableHasIdentity

    Table

    Table has an identity column.

    1 = True

    0 = False

    TableHasIndex

    Table

    Table has an index of any type.

    1 = True

    0 = False

    TableHasInsertTrigger

    Table

    Object has an INSERT trigger.

    1 = True

    0 = False

    TableHasNonclustIndex

    Table

    Table has a nonclustered index.

    1 = True

    0 = False

    TableHasPrimaryKey

    Table

    Table has a primary key.

    1 = True

    0 = False

    TableHasRowGuidCol

    Table

    Table has a ROWGUIDCOL for a uniqueidentifier column.

    1 = True

    0 = False

    TableHasTextImage

    Table

    Table has a text, ntext, or image column.

    1 = True

    0 = False

    TableHasTimestamp

    Table

    Table has a timestamp column.

    1 = True

    0 = False

    TableHasUniqueCnst

    Table

    Table has a UNIQUE constraint.

    1 = True

    0 = False

    TableHasUpdateTrigger

    Table

    Object has an UPDATE trigger.

    1 = True

    0 = False

    TableHasVarDecimalStorageFormat

    Table

    Table is enabled for vardecimal storage format.

    1 = True

    0 = False

    TableInsertTrigger

    Table

    Table has an INSERT trigger.

    >1 = ID of first trigger with the specified type.

    TableInsertTriggerCount

    Table

    Table has the specified number of INSERT triggers.

    >0 = The number of INSERT triggers.

    TableIsFake

    Table

    Table is not real. It is materialized internally on demand by the SQL Server Database Engine.

    1 = True

    0 = False

    TableIsLockedOnBulkLoad

    Table

    Table is locked due to a bcp or BULK INSERT job.

    1 = True

    0 = False

    TableIsPinned

    Table

    Table is pinned to be held in the data cache.

    0 = False

    This feature is not supported in SQL Server 2005 and later.

    TableTextInRowLimit

    Table

    Maximum bytes allowed for text in row.

    0 if text in row option is not set.

    TableUpdateTrigger

    Table

    Table has an UPDATE trigger.

    > 1 = ID of first trigger with the specified type.

    TableUpdateTriggerCount

    Table

    The table has the specified number of UPDATE triggers.

    > 0 = The number of UPDATE triggers.

    TableHasColumnSet

    Table

    Table has a column set.

    0 = False

    1 = True

    For more information, see Using Column Sets.

Return Types

int

Exceptions

Returns NULL on error or if a caller does not have permission to view the object.

A user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as OBJECTPROPERTY may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration and Troubleshooting Metadata Visibility.

Remarks

The Database Engine assumes that object_id is in the current database context. A query that references an object_id in another database will return NULL or incorrect results. For example, in the following query the current database context is the master database. The Database Engine will try to return the property value for the specified object_id in that database instead of the database specified in the query. The query returns incorrect results because the view vEmployee is not in the master database.

USE master;
GO
SELECT OBJECTPROPERTY(OBJECT_ID(N'AdventureWorks.HumanResources.vEmployee'), 'IsView');
GO

OBJECTPROPERTY(view_id, 'IsIndexable') may consume significant computer resources because evaluation of IsIndexable property requires the parsing of view definition, normalization, and partial optimization. Although the IsIndexable property identifies tables or views that can be indexed, the actual creation of the index still might fail if certain index key requirements are not met. For more information, see CREATE INDEX (Transact-SQL).

OBJECTPROPERTY(table_id, 'TableHasActiveFulltextIndex') will return a value of 1 (true) when at least one column of a table is added for indexing. Full-text indexing becomes active for population as soon as the first column is added for indexing.

When a table is created, the QUOTED IDENTIFIER option is always stored as ON in the metadata of the table, even if the option is set to OFF when the table is created. Therefore, OBJECTPROPERTY(table_id, 'IsQuotedIdentOn') will always return a value of 1 (true).

Examples

A. Verifying that an object is a table

The following example tests whether UnitMeasure is a table in the AdventureWorks database.

USE AdventureWorks;
GO
IF OBJECTPROPERTY (OBJECT_ID(N'Production.UnitMeasure'),'ISTABLE') = 1
   PRINT 'UnitMeasure is a table.'
ELSE IF OBJECTPROPERTY (OBJECT_ID(N'Production.UnitMeasure'),'ISTABLE') = 0
   PRINT 'UnitMeasure is not a table.'
ELSE IF OBJECTPROPERTY (OBJECT_ID(N'Production.UnitMeasure'),'ISTABLE') IS NULL
   PRINT 'ERROR: UnitMeasure is not a valid object.';
GO

B. Verifying that a scalar-valued user-defined function is deterministic

The following example tests whether the user-defined scalar-valued function ufnGetProductDealerPrice, which returns a money value, is deterministic.

USE AdventureWorks;
GO
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.ufnGetProductDealerPrice'), 'IsDeterministic');
GO

The result set shows that ufnGetProductDealerPrice is not a deterministic function.

-----
0

C. Finding the objects that belong to a specific schema

The following example uses the SchemaId property to return all the objects that belong to the schema Production.

USE AdventureWorks;
GO
SELECT name, object_id, type_desc
FROM sys.objects 
WHERE OBJECTPROPERTY(object_id, N'SchemaId') = SCHEMA_ID(N'Production')
ORDER BY type_desc, name;
GO