By: Greg Robidoux | Comments | Related: > Database Design
Problem
SQL Server stores a lot of data about your database objects in various places and in various formats. When pulling data about table columns this data can be pulled directly from the syscolumns table. Some of this information is useful as it is, but some of the data needs to be interpreted to understand. In addition to pulling data directly from syscolumns you can also use the information schema view. If you query from INFORMATION_SCHEMA.COLUMNS you get a lot of data in a useful format, but there is still some missing data. So how can you retrieve additional data about column level properties?
Solution
In addition to the INDEXPROPERTY and DATABASEPROPERTYEX functions, SQL Server also offers the COLUMNPROPERTY function to return column level property information. Most of this information is available in the INFORMATION_SCHEMA.COLUMNS view, but not all of this information is easily accessible.
Here are a couple of examples of how you can use this function.
To find a list of tables and the name of the identity column, you can issue the following command:
SELECT OBJECT_NAME(id) as ObjectName, Name as IdentityColumn FROM syscolumns WHERE COLUMNPROPERTY( id ,name, 'IsIdentity') = 1 ORDER BY 1, 2
Here is the output from this command when run against the AdventureWorks database.
Here is another example to find if there are any computed columns.
SELECT OBJECT_NAME(id) as ObjectName, Name as ComputedColumn FROM syscolumns WHERE COLUMNPROPERTY( id ,name, 'IsComputed') = 1 ORDER BY 1, 2
There are several other values that you can retrieve by using this function. The below list outlines the various items and also shows what is available in SQL Server 2000 vs SQL Server 2005.
| Value | SQL2000 | SQL2005 | Description | Value returned |
|---|---|---|---|---|
| AllowsNull | X | X | Allows null values. | 1 = TRUE 0 = FALSE NULL = Input is not valid. |
| ColumnId | X | Column ID value corresponding to sys.columns.column_id. | Column ID | |
| FullTextTypeColumn | X | The TYPE COLUMN in the table that holds the document type information of the column. | ID of the full-text TYPE COLUMN for the column passed as the second parameter of this property. | |
| IsComputed | X | X | Column is a computed column. | 1 = TRUE 0 = FALSE NULL = Input is not valid. |
| IsCursorType | X | X | Procedure parameter is of type CURSOR. | 1 = TRUE 0 = FALSE NULL = Input is not valid. |
| IsDeterministic | X | X | Column is deterministic. This property applies only to computed columns and view columns. | 1 = TRUE 0 = FALSE NULL = Input is not valid. Not a computed column or view column. |
| IsFulltextIndexed | X | X | Column has been registered for full-text indexing. | 1 = TRUE 0 = FALSE NULL = Input is not valid. |
| IsIdentity | X | X | Column uses the IDENTITY property. | 1 = TRUE 0 = FALSE NULL = Input is not valid. |
| IsIdNotForRepl | X | X | Column checks for the IDENTITY_INSERT setting. If IDENTITY NOT FOR REPLICATION is specified, the IDENTITY_INSERT setting is not checked. | 1 = TRUE 0 = FALSE NULL = Input is not valid. |
| IsIndexable | X | X | Column can be indexed. | 1 = TRUE 0 = FALSE NULL = Input is not valid. |
| IsOutParam | X | X | Procedure parameter is an output parameter. | 1 = TRUE 0 = FALSE NULL = Input is not valid. |
| IsPrecise | X | X | Column is precise. This property applies only to deterministic columns. | 1 = TRUE 0 = FALSE NULL = Input is not valid. Not a deterministic column |
| IsRowGuidCol | X | X | Column has the uniqueidentifier data type and is defined with the ROWGUIDCOL property. | 1 = TRUE 0 = FALSE NULL = Input is not valid. |
| IsSystemVerified | X | The determinism and precision properties of the column can be verified by the SQL Server 2005 Database Engine. This property applies only to computed columns and columns of views. | 1 = TRUE 0 = FALSE NULL = Input is not valid. |
|
| IsXmlIndexable | X | The XML column can be used in an XML index | 1 = TRUE 0 = FALSE NULL = Input is not valid. |
|
| Precision | X | X | Length for the data type of the column or parameter. | The length of
the specified column data type -1 = xml or large value types NULL = Input is not valid. |
| Scale | X | X | Scale for the data type of the column or parameter. | The scale NULL = Input is not valid. |
| SystemDataAccess | X | Column is derived from a function that accesses data in the system catalogs or virtual system tables of SQL Server. This property applies only to computed columns and columns of views. | 1 = TRUE (Indicates
read-only access.) 0 = FALSE NULL = Input is not valid. |
|
| UserDataAccess | X | Column is derived from a function that accesses data in user tables, including views and temporary tables, stored in the local instance of SQL Server. This property applies only to computed columns and columns of views. | 1 = TRUE (Indicates
read-only access.) 0 = FALSE NULL = Input is not valid. |
|
| UsesAnsiTrim | X | X | ANSI_PADDING was set ON when the table was first created. This property applies only to columns or parameters of type char or varchar. | 1= TRUE 0= FALSE NULL = Input is not valid. |
(Source SQL Server 2000 and SQL Server 2005 Books Online)
Next Steps
- Take a look at this built-in database function and how you can use it to document your servers or easily find out the settings across all of your databases
- Use this function to audit your servers to see what has changed
- Take a look at these other functions that return property information
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
