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.
.gif "Ikona łącza do tematu")
Transact-SQL Syntax Conventions
Składnia
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.[!UWAGA]
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.
[!UWAGA]
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.
[!UWAGA]
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 Use Column Sets.
Return Types
int
Wyjątki
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.
Uwagi
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'AdventureWorks2012.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 AdventureWorks2012 database.
USE AdventureWorks2012;
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 AdventureWorks2012;
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 AdventureWorks2012;
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
Zobacz także
Odwołanie
Metadata Functions (Transact-SQL)
OBJECTPROPERTYEX (Transact-SQL)