Use Property Functions to Query Metadata

nce you get past the point of creating a solid database design and ensuring the integrity of your database, inevitably you need to know about the objects in your database. SQL Server has a variety of objects that track and access metadata?the information about database objects. These objects include system tables, system stored procedures, and information schema views.

Before version 7 was available, getting to this metadata meant creating complex routines to query the system tables and decode bitmask fields. But now there are three property functions for querying the attributes of database objects: OBJECTPROPERTY, COLUMNPROPERTY, and DATABASEPROPERTY. In this 10-Minute Solution, I take a look at the OBJECTPROPERTY function and describe the kind of data you can retrieve with it.

The OBJECTPROPERTY function takes two parameters: the object ID and the property name. In most cases this function returns either a 1 (True) or a 0 (False), except in some cases where an object ID is returned. Here is the syntax of this function:

OBJECTPROPERTY(ObjectID, PropertyName)

Every object created in a SQL Server database has a unique ID, all of which are stored in the sysobjects system table. The first input parameter of this function is an integer containing the unique ID of the object you want to find out about. The PropertyName input parameter is the string value of the property you want to query. This code returns whether the authors trigger in the Pubs database has a primary key:

SELECT OBJECTPROPERTY(OBJECT_ID('authors'), 'TableHasPrimaryKey')

You most likely won’t know the ID of the object you want to know about but you’ll know at least its name. Therefore, you can use the OBJECT_ID function, which returns the unique ID for a given object name. If you pass the OBJECTPROPERTY function an invalid object ID or property name, it returns a NULL but it won’t generate an error.

What kind of information can you find out with OBJECTPROPERTY? Anything from whether an object is a table (IsTable) to whether a table has an insert trigger (TableHasInsertTrigger). Over 30 of the 50 properties query various table attributes. You can find out if a table has an index, a foreign key, a primary key, a timestamp column, and so on. See Table 1 for the full list. (All property names return 0 or 1, except where noted.) You can use OBJECTPROPERTY anywhere you call standard functions, such as stored procedures or triggers.

Table 1. Property Name Values Used with the OBJECTPROPERTY Function

Name

Description

CnstIsClustKey

Returns whether or not the specified constraint is a primary key with a clustered index

CnstIsColumn

Returns whether or not the constraint is a column constraint (as opposed to a table constraint)

CnstIsDisabled

Returns whether or not the constraint is disabled

CnstIsNonclustKey

Returns whether or not the constraint is a primary key with a non-clustered index

CnstIsNotRepl

Returns whether or not the constraint is defined as not enabled during replication

ExecIsAnsiNullsOn

A stored procedure does not allow null comparisons

ExecIsDeleteTrigger

Returns whether or not the trigger is a delete trigger

ExecIsInsertTrigger

Returns whether or not the trigger is an insert trigger

ExecIsQuotedIdentOn

A stored procedure accepts double quotes around identifiers and single quotes around literals

ExecIsStartup

Returns whether or not the stored procedure executes automatically when the SQL Server service starts

ExecIsTriggerDisabled

Returns whether or not the trigger is disabled

ExecIsUpdateTrigger

Returns whether or not the trigger is an update trigger

IsCheckCnst

Returns whether or not the object is a check constraint

IsConstraint

Returns whether or not the object is a constraint of some kind (primary key, check constraint, foreign key, etc.)

IsDefault

Returns whether or not the object is a bound default

IsDefaultCnst

Returns whether or not the object is a default constraint

IsExecuted

Returns whether or not the specified object can be executed as a trigger, stored procedure, or view

IsExtendedProc

Returns whether or not the specified object is an extended stored procedure

IsForeignKey

Returns whether or not the specified object is a foreign key constraint

IsMSShipped

Returns whether or not the object is a system object shipped with SQL Server; this is determined by checking whether the object’s system bit has been set

IsPrimaryKey

Returns whether or not the object is a primary key constraint

IsProcedure

Returns whether or not the object is a stored procedure (returns False if the object is a trigger)

IsReplProc

Returns whether or not the stored procedure is a replication procedure

IsRule

Returns whether or not the object is a database rule

IsSystemTable

Returns whether or not the object is a system table

IsTable

Returns whether or not the specified object is a table

IsTrigger

Returns whether or not the object is a trigger

IsUniqueCnst

Returns whether or not the object is a unique constraint

IsUserTable

Returns whether or not the object is a user-defined table

IsView

Returns whether or not the object is a view

OwnerId

Returns the ID of the object owner

TableDeleteTrigger

Returns the object ID of the table’s delete trigger

TableDeleteTriggerCount

Returns the number of delete triggers associated with a table

TableFulltextCatalogId

Returns the object ID of the catalog that contains the full-text indexes for a table

TableHasActiveFulltextIndex

Returns whether or not the table contains a full-text index

TableHasCheckCnst

Returns whether or not the table has at least one check constraint

TableHasClustIndex

Returns whether or not the table has a clustered index

TableHasDefaultCnst

Returns whether or not the table has at least one default constraint

TableHasDeleteTrigger

Returns whether or not the table has a delete trigger

TableHasForeignKey

Returns whether or not the table has at least one foreign key

TableHasIdentity

Returns whether or not the table contains a column of type Identity

TableHasIndex

Returns whether or not the table has at least one index (returns true if the table has a primary key)

TableHasInsertTrigger

Returns whether or not the table has an insert trigger

TableHasNonclustIndex

Returns whether or not the table has at least one non-clustered index

TableHasPrimaryKey

Returns whether or not the table has a primary key

TableHasRowGuidCol

Returns whether or not the table has a column defined as a RowGUID column

TableHasTextImage

Returns whether or not the table contains a column of datatype text

TableHasTimestamp

Returns whether or not the table has a timestamp column

TableHasUniqueCnst

Returns whether or not the table has a unique constraint

TableHasUpdateTrigger

Returns whether or not the table has an update trigger

TableInsertTrigger

Returns the object ID of the table’s insert trigger

TableInsertTriggerCount

Returns the number of insert triggers associated with a table

TableIsFake

Returns whether or not the table is not a base table but created internally by SQL Server

TableIsPinned

Returns whether or not the table is pinned and maintained in memory

TableUpdateTrigger

Returns the object ID of the table’s update trigger

TableUpdateTriggerCount

Returns the number of update triggers associated with a table

TriggerDeleteOrder

Returns the order in which this delete trigger fires

TriggerInsertOrder

Returns the order in which this insert trigger fires

TriggerUpdateOrder

Returns the order in which this update trigger fires

Although you can return object IDs for objects in other databases by specifying the database?

SELECT OBJECT_ID('Northwind..Categories')

?the OBJECTPROPERTY function doesn’t work across databases so you must always be sure you’re using the correct database.

Stay tuned for my next 10-Minute Solution, where I’ll look at the column and database information you can query with the other property functions.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may

man on floor with data

DevX Quick Guide to Data Ingestion

One of the biggest trends of the 21st century is the massive surge in internet usage. With major innovations such as smart technology, social media, and online shopping sites, the internet has become an essential part of everyday life for a large portion of the population. Due to this internet

payment via phone

7 Ways Technology Has Changed Traditional Payments

In today’s digital world, technology has changed how we make payments. From contactless cards to mobile wallets, it’s now easier to pay for goods and services without carrying cash or using a checkbook. This article will look at seven of the most significant ways technology has transformed traditional payment methods.