SQL Basics - Finding identity columns
After a bit of delay I'm back with the last installment of SQL Basics. I planned to write this blog several times over the last few days but to be honest I had other interests. My daughter said her first words this week, which just happened to be "dada", and has wanted to play all evening. How do you say no to that?
So today I'm talking about identity columns and how to find them in your database. I'm not going to get into the debate over whether or not you should use identity columns, I'm just pointing you to where they are. :)
If you read the last two posts you will recognize that we are headed down similar paths for finding identity columns. We will be focusing on a couple of the catalog views as well as making use of a system function. For the purposes of this blog I'm returning the following detail from each query:
- Table name
- Column name
- Data type
- Current identity value
Option 1 is sys.identity_columns, a catalog view returning a row for each object with an identity column. This view was introduced in SQL 2005 and inherits from the sys.columns catalog view. The syntax is:
Option 2 is sys.columns, a catalog view returning a row for all objects that have columns. The syntax for this view is:
Option 3 is sys.syscolumns, a compatibility view returning a row for each table, view, and stored procedure in your database. The syntax for this view is:
Option 4 makes use of the OBJECTPROPERTY system function. This function returns a wealth of information about objects. Note that the scope of this function is database specific. The one drawback to this option is that the return of the column name and data type are not easily done without a join to one of the objects above, which would defeat the point. But if you are only looking for tables that contain identity columns this is a good option. Here is the syntax:
So there are four ways to get at identity columns in your database. I hope this series of posts has been helpful. If there are other topics you would like to see drop me an email, otherwise we headed back to SQL 2008 in anticipation of next week's launch event here in Atlanta.
-W