n my last 10-Minute Solution, “Use Property Functions to Query Metadata“, I looked at OBJECTPROPERTY, one of the property functions in SQL Server. Property functions are new to SQL Server 7.0 and are used to query various details or properties about the objects in your database. In this 10-Minute Solution, I take a look at the COLUMNPROPERTY and DATABASEPROPERTY functions.
The COLUMNPROPERTY Function
As you can probably guess, the COLUMNPROPERTY function returns information about table columns, but it also returns details about stored procedure parameters. The COLUMNPROPERTY function takes three parameters: the ObjectID of the table or stored procedure, the name of the column or parameter, and the property name that is the attribute you want to query. For example, this code queries a column in the Categories table as to whether or not it allows null values:
USE NorthwindSELECT (CASE COLUMNPROPERTY(OBJECT_ID('Categories'), 'CategoryID', 'AllowsNull') WHEN 1 THEN 'True' ELSE 'False' END) AS 'AllowsNulls'Just like the OBJECTPROPERTY function, COLUMNPROPERTY doesn’t work across databases, so you must specify the correct database first. COLUMNPROPERTY returns True or False, except when checking for Precision or Scale—in which case it returns the precision or scale value for the column or parameter. Table 1 lists all the property attributes that can be returned with COLUMNPROPERTY, as well as which ones are specific to SQL Server 2000.
Table 1. Property Name Values Used with the COLUMNPROPERTY Function | ||
Property | SQL Server 2000 Only | Description |
AllowsNull | Returns whether or not the column’s nullability is set to allow null values | |
IsComputed | Returns whether or not the column is computed with the values of one or more columns | |
IsCursorType | Returns whether or not the parameter is of data type cursor | |
IsDeterministic | x | Returns whether or not a computed column is deterministic. |
IsFulltextIndexed | Returns whether or not the column is used in full-text indexing | |
IsIdentity | Returns whether or not the column has its Identity attribute set for automatic numbering | |
IsIdNotForRepl | Returns whether or not an identity column’s values should be re-seeded during replication or replicated with the explicit values of the column | |
IsIndexable | x | Returns whether or not the column can be indexed |
IsOutParam | Returns whether or not a stored procedure parameter is an output parameter | |
IsPrecise | x | The column is precise (deterministic columns only) |
IsRowGuidCol | Returns whether or not a column is of data type uniqueidentifier and has its ROWGUID property set | |
Precision | Returns the precision for a column or parameter | |
Scale | Returns the scale for a column or parameter | |
UsesAnsiTrim | Returns whether or not the SQL-92 ANSI padding setting was on when the table was created | |
The DATABASEPROPERTY Function
Appropriately, the DATABASEPROPERTY function returns attributes for a database and takes two parameters: the database name and the property name. Use this syntax to query whether or not the specified database uses the SQL-92 standard for null values:
USE NorthwindSELECT DATABASEPROPERTY('Northwind', 'IsAnsiNullDefault')Note that in SQL Server 2000, the DATABASEPROPERTY function is replaced by the DATABASEPROPERTYEX function. The old function name can still be used—but is provided for backward compatibility only—so you’ll want to update any references to the new function name. SQL Server 2000 provides quite a few new property name values and, in some cases, condenses multiple property names into a single one. For example, the IsInStandBy, IsInLoad, and IsInRecovery property names are no longer used with the DATABASEPROPERTYEX function; instead there’s a single Status property name and the function’s return value is the database status (see Table 2).
Table 2. Property Name Values Used with the DATABASEPROPERTY(EX) Function | |||
Property | SQL Server 7.0 Only | SQL Server 2000 Only | Description |
Collation | x | Returns the name of the default collation for the database | |
IsAnsiNullDefault | Returns whether or not the database uses the SQL-92 standard for allowing nulls | ||
IsAnsiNullsEnabled | Returns whether or not comparisons that include a null value evaluate to unknown | ||
IsAnsiPaddingEnabled | x | Returns whether or not string values are padded to the same length when they are compared or inserted | |
IsAnsiWarningsEnabled | Returns whether or not error and warning messages are issued when an error is encountered | ||
IsArithmeticAbortEnabled | x | Returns whether or not queries are terminated if an overflow or divide-by-zero error occurs | |
IsAutoClose | Returns whether or not the database shuts down automatically after the last user exits | ||
IsAutoCreateStatistics | x | Returns whether or not the statistics are updated automatically when they become out of date due to table changes | |
IsAutoShrink | Returns whether or not the database files are shrunk automatically | ||
IsAutoUpdateStatistics | Returns whether or not the automatic updating of statistics is enabled on a database | ||
IsBulkCopy | x | Returns whether or not the database allows non-logged operations | |
IsCloseCursorsOnCommitEnabled | Returns whether or not open cursors are closed when a transaction is committed | ||
IsDboOnly | x | Returns whether or not the database is in DBO-only access mode | |
IsDetached | x | Returns whether or not the database was detached | |
IsEmergencyMode | x | Returns whether or not emergency mode is enabled so a suspect database can be used | |
IsFulltextEnabled | Returns whether or not the database is enabled for full-text indexing | ||
IsInLoad | x | Returns whether or not the database is currently loading. | |
IsInRecovery | x | Returns whether or not the database is currently recovering | |
IsInStandBy | Returns whether or not the database is online as read-only | ||
IsLocalCursorsDefault | Returns whether or not declared cursors are local | ||
IsMergePublished | x | Returns whether or not tables in the database can be published for replication | |
IsNotRecovered | x | Returns whether or not the database failed to recover | |
IsNullConcat | Returns whether or not concatenation of a value with a null returns a null | ||
IsNumericRoundAbortEnabled | x | Returns whether or not an error is generated when a loss of precision occurs | |
IsOffline | x | Returns whether or not the database is offline. | |
IsQuotedIdentifiersEnabled | Returns whether or not double quotation marks can be used on identifiers | ||
IsReadOnly | x | Returns whether or not the database is read-only | |
IsRecursiveTriggersEnabled | x | Returns whether or not the recursive firing of triggers is enabled | |
IsShutDown | x | Returns whether or not the database encountered a problem at startup | |
IsSingleUser | x | Returns whether or not the database is in single-user mode | |
IsSubscribed | x | Returns whether or not the database can be subscribed for replication | |
IsSuspect | x | Returns whether or not the database is suspect | |
IsTornPageDetectionEnabled | x | Returns whether or not the database detects incomplete I/O operations | |
IsTruncLog | x | Returns whether or not the database truncates its log on checkpoints | |
Recovery | x | Returns the recovery mode for the database: FULL, BULK_LOGGED, or SIMPLE | |
SQLSortOrder | x | Returns zero for Windows collation or the ID value of the sort order | |
Status | x | Returns the database status of ONLINE, OFFLINE, RESTORING, RECOVERING, or SUSPECT | |
Updateability | x | Returns READ_ONLY for read-only databases or READ_WRITE for modifiable databases | |
UserAccess | x | Returns which users can access the database: SINGLE_USER for only one user at a time; RESTRICTED_USER for members of db_owner, dbcreator, or sysadmin roles; or MULTI_USER for all users | |
Version | Returns the internal SQL Server version number if the database is open or null if it’s closed | ||
