Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » Meta Data FunctionsRSS Feeds

OBJECTPROPERTY Function

Posted By: Sarita Patel     Category: SQL Server     Views: 7211

This article explains about OBJECTPROPERTY function in sql server with examples.

OBJECTPROPERTY function is used to get information of objects in the current database.



Syntax of OBJECTPROPERTY Function :

OBJECTPROPERTY ( id, property )

id is an identification number of object in the current database. It is of type integer.

property is a property name. It can be of below values.

Property Name

Object Type

Description and returned value

CnstIsClustKey

Constraint

A primary key with a clustered index.

1 = True
0 = False

CnstIsColumn

Constraint

COLUMN constraint.

1 = True
0 = False

CnstIsDeleteCascade

Constraint

A foreign key constraint with the ON DELETE CASCADE option.

CnstIsDisabled

Constraint

Disabled constraint.

1 = True
0 = False

CnstIsNonclustKey

Constraint

A primary key with a nonclustered index.

1 = True
0 = False

CnstIsNotTrusted

Constraint

Constraint was enabled without checking existing rows, so constraint may not hold for all rows.

1 = True 
0 = False

CnstIsNotRepl

Constraint

The constraint is defined with the NOT FOR REPLICATION keywords.

CnstIsUpdateCascade

Constraint

A foreign key constraint with the ON UPDATE CASCADE option.

ExecIsAfterTrigger

Trigger

AFTER trigger.

ExecIsAnsiNullsOn

Scalar and Inline Table-valued Function, Procedure, Trigger, View

The setting of ANSI_NULLS at creation time.

1 = True
0 = False

ExecIsDeleteTrigger

Trigger

DELETE trigger.

1 = True
0 = False

ExecIsFirstDeleteTrigger

Trigger

The first trigger fired when a DELETE is executed against the table.

ExecIsFirstInsertTrigger

Trigger

The first trigger fired when an INSERT is executed against the table.

ExecIsFirstUpdateTrigger

Trigger

The first trigger fired when an UPDATE is executed against the table.

ExecIsInsertTrigger

Trigger

INSERT trigger.

1 = True
0 = False

ExecIsInsteadOfTrigger

Trigger

INSTEAD OF trigger.

ExecIsLastDeleteTrigger

Trigger

The last trigger fired when a DELETE is executed against the table.

ExecIsLastInsertTrigger

Trigger

The last trigger fired when an INSERT is executed against the table.

ExecIsLastUpdateTrigger

Trigger

The last trigger fired when an UPDATE is executed against the table.

ExecIsQuotedIdentOn

Scalar and Inline Table-valued Function, Procedure, Trigger, View

The 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

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

Scalar and Inline Table-valued Function, Procedure, Table, Trigger, View

Specifies that the ANSI NULLS option setting for the table is ON, meaning 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 = ON
0 = OFF

IsCheckCnst

Any

CHECK constraint.

1 = True
0 = False

IsConstraint

Any

Constraint.

1 = True
0 = False

IsDefault

Any

Bound default.

1 = True
0 = False

IsDefaultCnst

Any

DEFAULT constraint.

1 = True
0 = False

IsDeterministic

Function, View

The determinism property of the function or view. Applies to scalar- and table-valued functions and views.

1 = Deterministic
0 = Not Deterministic
NULL = Not a function or view, or invalid object ID.

IsExecuted

Any

Specifies the object can be executed (view, procedure, function, or trigger).

1 = True
0 = False

IsExtendedProc

Any

Extended procedure.

1 = True
0 = False

IsForeignKey

Any

FOREIGN KEY constraint.

1 = True
0 = False

IsIndexed

Table, View

A table or view with an index.

IsIndexable

Table, View

A table or view on which an index may be created.

IsInlineFunction

Function

Inline function.

1 = Inline function
0 = Not inline function
NULL = Not a function, or invalid object ID.

IsMSShipped

Any

An object created during installation of Microsoft® SQL Server™ 2000.

1 = True
0 = False

IsPrimaryKey

Any

PRIMARY KEY constraint.

1 = True
0 = False

IsProcedure

Any

Procedure.

1 = True
0 = False

IsQuotedIdentOn

Scalar and Inline Table-valued Function, Procedure, Table, Trigger, View, CHECK Constraint, DEFAULT Definition

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

1 = ON
0 = OFF

IsReplProc

Any

Replication procedure.

1 = True
0 = False

IsRule

Any

Bound rule.

1 = True
0 = False

IsScalarFunction

Function

Scalar-valued function.

1 = Scalar-valued
0 = Table-valued
NULL = Not a function, or invalid object ID.

IsSchemaBound

Function, View

A schema bound function or view created with SCHEMABINDING.

1 = Schema-bound
0 = Not schema-bound
NULL = Not a function or a view, or invalid object ID.

IsSystemTable

Table

System table.

1 = True
0 = False

IsTable

Table

Table.

1 = True
0 = False

IsTableFunction

Function

Table-valued function.

1 = Table-valued
0 = Scalar-valued
NULL = Not a function, or invalid object ID.

IsTrigger

Any

Trigger.

1 = True
0 = False

IsUniqueCnst

Any

UNIQUE constraint.

1 = True
0 = False

IsUserTable

Table

User-defined table.

1 = True
0 = False

IsView

View

View.

1 = True
0 = False

OwnerId

Any

Owner of the object.

Nonnull = The database user ID of the object owner.
NULL = Invalid input.

TableDeleteTrigger

Table

Table has a DELETE trigger.

>1 = ID of first trigger with given type.

TableDeleteTriggerCount

Table

The table has the specified number of DELETE triggers.

>0 = The number of DELETE triggers.
NULL = Invalid input.

TableFullTextBackgroundUpdateIndexOn

Table

The table has full-text background update index enabled.

1 = TRUE
0 = FALSE

TableFulltextCatalogId

Table

The 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 is not full-text indexed.

TableFullTextChangeTrackingOn

Table

The table has full-text change-tracking enabled.

1 = TRUE
0 = FALSE

TableFulltextKeyColumn

Table

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

0 = Table is not full-text indexed.

TableFullTextPopulateStatus

Table

0 = No population
1 = Full population
2 = Incremental population

TableHasActiveFulltextIndex

Table

The table has an active full-text index.

1 = True
0 = False

TableHasCheckCnst

Table

The table has a CHECK constraint.

1 = True
0 = False

TableHasClustIndex

Table

The table has a clustered index.

1 = True
0 = False

TableHasDefaultCnst

Table

The table has a DEFAULT constraint.

1 = True
0 = False

TableHasDeleteTrigger

Table

The table has a DELETE trigger.

1 = True
0 = False

TableHasForeignKey

Table

The 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

The table has an identity column.

1 = True
0 = False

TableHasIndex

Table

The table has an index of any type.

1 = True
0 = False

TableHasInsertTrigger

Table

The object has an Insert trigger.

1 = True
0 = False
NULL = Invalid input.

TableHasNonclustIndex

Table

The table has a nonclustered index.

1 = True
0 = False

TableHasPrimaryKey

Table

The table has a primary key.

1 = True
0 = False

TableHasRowGuidCol

Table

The table has a ROWGUIDCOL for auniqueidentifier column.

1 = True
0 = False

TableHasTextImage

Table

The table has a text column.

1 = True
0 = False

TableHasTimestamp

Table

The table has a timestamp column.

1 = True
0 = False

TableHasUniqueCnst

Table

The table has a UNIQUE constraint.

1 = True
0 = False

TableHasUpdateTrigger

Table

The object has an Update trigger.

1 = True
0 = False

TableInsertTrigger

Table

The table has an INSERT trigger.

>1 = ID of first trigger with given type.

TableInsertTriggerCount

Table

The table has the specified number of INSERT triggers.

>0 = The number of INSERT triggers.
NULL = Invalid input.

TableIsFake

Table

The table is not real. It is materialized internally on demand by SQL Server.

1 = True
0 = False

TableIsLockedOnBulkLoad

Table

The table is locked due to a BCP or BULK INSERT job.

1 = True
0 = False

TableIsPinned

Table

The table is pinned to be held in the data cache.

1 = True
0 = False

TableTextInRowLimit

Table

The maximum bytes allowed for text in row, or 0 if text in row option is not set.

TableUpdateTrigger

Table

The table has an UPDATE trigger.

>1 = ID of first trigger with given type.

TableUpdateTriggerCount

Table

The table has the specified number of UPDATE triggers.

>0 = The number of UPDATE triggers.
NULL = Invalid input.


Return type of OBJECTPROPERTY function is an integer.




Examples of OBJECTPROPERTY Function :

Example 1 : Use of OBJECTPROPERTY function in select clause

SELECT OBJECTPROPERTY( OBJECT_ID('Customers'), 'IsUserTable' )

Output
1

Above example returns 1 means Customers is a user defined table.
  
Share: 

 
 

Didn't find what you were looking for? Find more on OBJECTPROPERTY Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of OBJECTPROPERTY Function is from United States.
 
View All Articles

Related Articles and Code:


 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!