Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Retrieving SQL Server Column Properties with COLUMNPROPERTY


By:   |   Last Updated: 2007-06-28   |   Comments   |   Related Tips: More > 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 Activity

  • 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


Last Updated: 2007-06-28


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools