How do I identify whether a column is set up to automatically increment

How do I programmatically identify whether a column is set up to automatically increment? I am looking for a field in the syscolumns table which identifies whether a referenced column is not updatable because it set up to auto increment. Thanks.Check out syscolumns in bol. The value is stored in status as hex 0x80 or 128 (decimal) as a bit flag. Also, you might be able to use the colstat field in syscolumns (1 = identity) but I have not found supported documentation - which means it may be that today but not tomorrow.|||Q1 How do I programmatically identify whether a column is set up to automatically increment?
I am looking for a field in the syscolumns table which identifies whether a referenced column is not updatable because it set up to auto increment. Thanks.

A1 Use sp_columns (check the Type_Name result set column). For example:

exec sp_columns
@.table_name = 'YourTable',
@.column_name = 'MysteryColumn'

You could also use the third result set of sp_help (or sp_columns more generally):

USE pubs
EXEC sp_help jobs
EXEC sp_columns jobs
EXEC sp_columns @.table_name = 'jobs', @.column_name = 'job_id'

-- compare to (no identity column)
EXEC sp_help authors
EXEC sp_columns authors|||The sp_columns/sp_help just uses the syscolumns database - so if you are looking for a specific answer use the bit flag from syscolumns. Using sp_columns/sp_help adds another layer of complexity that you can pull directly from syscolumns. As a matter of fact, sp_help uses the colstat column to determine an identity column - again this is undocumentated but used with sp_help.

You can use the following as a template:

select a.name, b.name from syscolumns as a inner join sysobjects as b on a.id = b.id where a.status & 0x80 > 0

It will return the column name and object name that has an identity field. You will need to fine tune this for you scenario - but you will be able to return a count or other specific information directly.

Good luck.|||RE:
The sp_columns/sp_help just uses the syscolumns database - so if you are looking for a specific answer use the bit flag from syscolumns. Using sp_columns/sp_help adds another layer of complexity that you can pull directly from syscolumns. As a matter of fact, sp_help uses the colstat column to determine an identity column - again this is undocumentated but used with sp_help.

Selecting directly from system tables, (and not isolating user stored procedurees / applications from changes to underlying system tables in any way) may add multiple layers of "complexity" (in the form of maintenance checks and tasks to perform with every Sql Server service pack), as well. Such practices have also resulted in worse, (in the form of addressing / correcting corrupt data, and troubleshooting stored procedurees / applications that "mysteriously" began to malfunction and generate corrupt data and / or corrupt existing data), following the application of Sql Server upgrades that alter system tables.

Adding a layer of abstraction is exactly the point. Doing so in an organized manner often provides significant benefits in regard to minimizing support and maintenance resource use, and costs (especially in relation to addressing and correcting corrupt data, which may cause a business irreparable damage). If selecting directly from system tables is unavoidable (or using sp_columns/sp_help adds "too much" complexity); consider centralizing maintenance issues by providing your own private level of abstraction e.g.(sp_TableIdentityColumns).

Specifically: Consider creating your own user special stored procedures / functions (that select directly from system tables) and calling them in any other user stored procedures and applications. That way, (when Sql Server upgrades, service pack, or hot fix changes alter the underlying system tables radically), you need only change a few user special stored procedures / functions (rather than every procedure / application that calls / uses them).|||Its always recommended to lookup at INFORMATION SCHEMA VIEWS and not to query against SYSTEM tables. REfer to Books online for more information .

HTH
Originally posted by RickLambert
How do I programmatically identify whether a column is set up to automatically increment? I am looking for a field in the syscolumns table which identifies whether a referenced column is not updatable because it set up to auto increment. Thanks.|||RE: Its always recommended to lookup at INFORMATION SCHEMA VIEWS and not to query against SYSTEM tables. REfer to Books online for more information .
HTH

That would normally have been one reccomendation / suggestion; but, I do not know of any Information_Schema View (COLUMNS, TABLES, and TABLE_CONSTRAINTS, etc.) that provides identity column information?

Could you please share where identity column information is available in the Information_Schema views?? (If it is there, I would appreciate knowing where it may be found. - Thanks.)

Information_Schema views:

CHECK_CONSTRAINTS
COLUMNS
COLUMN_DOMAIN_USAGE
COLUMN_PRIVILEGES
CONSTRAINT_COLUMN_USAGE
CONSTRAINT_TABLE_USAGE
DOMAINS
DOMAIN_CONSTRAINTS
KEY_COLUMN_USAGE
PARAMETERS
REFERENTIAL_CONSTRAINTS
ROUTINES
ROUTINE_COLUMNS
SCHEMATA
TABLES
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
VIEWS
VIEW_COLUMN_USAGE
VIEW_TABLE_USAGE|||Thank you everyone for your response. This web site is a most impressive resource! So, I think I will use the ColStat=1, since this appears to be the most straightforward approach and my paranoia level is not very high. My objective is to create a view which contains a list of all columns and their characteristics, for the purpose of programmatically building insert, update, and delete stored procedures. Thanks again!|||I am curious if you are accessing this data exclusively in sql server or are you going to have an application access this data - say through visual basic or c++. Also, is it possible that you would like to have this functionality accessible to all databases or will it be isolated to one ?|||Hi rnealejr:

I am using an MS Access Data Project with SQL Server data to build SQL Server-specific stored procedures. However stored procedure naming and the parameters passed will remain constant regardless of the underlying database.

So I could use a similar approach to create Oracle stored procedures which would be referenced identically in code; just the connect string would change.

Similarly, the process of building the stored procedures is not hard-coded. A table contains the db-specific syntax for each type of stored procedure (insert-update-delete). Then this is is used by the sp-building routine which uses ADO to cycle through each of the rows in the view containing all the column characteristics of every table of the current database.

By the way, is there an easy way to determine the unique identifier of each table?

-RAL|||Since you are using ADO you could use the information from the provider and determine whether a column is an identity column (know that with ADO the provider has a wealth of information that may not be obvious) - but that may not be appropriate in this case. How do you compile these dynamic stored procedures ? Or is it just the syntax you are dynamically creating ? Can you give an example of the process ?

My other suggestion is to create an Information_Schema view - this would allow you to store the view in one location but run it in the context of the any (current) database.

Are you referring to the uniqueidentifier data type ?|||You can use the Information_Schema.columns view. Look under the DATA_TYPE column.|||Originally posted by rnealejr
Since you are using ADO you could use the information from the provider and determine whether a column is an identity column (know that with ADO the provider has a wealth of information that may not be obvious) - but that may not be appropriate in this case. How do you compile these dynamic stored procedures ? Or is it just the syntax you are dynamically creating ? Can you give an example of the process ?

My other suggestion is to create an Information_Schema view - this would allow you to store the view in one location but run it in the context of the any (current) database.

Are you referring to the uniqueidentifier data type ?

By unique identifier I meant primary key. I think this can be extracted using a view joining sysObjects-sysIndexes-sysIndexKeys.

Not sure how ADO would know how to build these stored procedures without reference to an appropriate view.

Here is an example of the syntax stored in the table referenced by the ADO code:

Create Procedure s_Insert_<<TableName>>
(
<<ParameterList>>
)

As
set nocount on

Insert Into dbo.<<TableName>> (
<<FieldList>>

) Values (

<<ValueList>>
)
return|||Originally posted by rnealejr
You can use the Information_Schema.columns view. Look under the DATA_TYPE column.

I am not familiar with how to access this programmatically.|||RE:
By unique identifier I meant primary key. I think this can be extracted using a view joining sysObjects-sysIndexes-sysIndexKeys.
...
Information_Schema.columns view. Look under the DATA_TYPE column.
I am not familiar with how to access this programmatically.


Q1 [How may one identify ALL unique (and candidate keys, including compound keys) columns in a table?]
A1 MS Sql Server 2k and earlier implement unique columns at the DBMS level via indices. (Looking at a table object's indices is therefore a good way to find columns that are implemented as such using built in DBMS methods. However privately maintained unique columns that do not use built in DBMS functionality to guarantee unique row values may not necessarily be identifiable using this approach.)

An example (to identify ALL unique columns (candidate keys, including compound keys):
Use Northwind
Go
exec sp_HelpIndex @.objname = 'Orders'
---

Q2 [RE: The Information_Schema.columns view; I am not familiar with how to access this programmatically?]
A2 For an example, run:

Use Northwind
Go
Select TABLE_NAME, COLUMN_NAME, DATA_TYPE
From [Information_Schema].[columns]
Where
[TABLE_NAME] = 'Orders'|||Thanx, DBA!|||Thanx, DBA!
You are welcome; hopefully some of it will help you create more robust apps.|||In a similar question, how do I find out if there are any table(s) that using IDENTITY column or numerical column as an IDENTITY, and using it to figure out if the column will reach the Max. value (like the SSN) very soon ?

Thanks|||You can use the following:

DBCC CHECKIDENT ('table_name', NORESEED)

What do you mean by max value - 2,147,483,647 ? Which data type are you using for your identity int or bigint (ss2k only) ?|||or use IDENT_CURRENT('table_name')|||Thanks rnealejr. I am aware of the DBCC CHECKIDENT and IDENT_CURRENT function, but what if the column is NOT employed the IDENTITY but other numeric data type (int or bigint) ? For example, the PurchaseOrder column is using INT as data type and it started at seed 2,000,000,000 (2 billion), and increment by 1000 ... I wanted to find out if such column is exists and how soon it will reach the Max value of INT.

Thanks|||So you have other columns that are not identity columns but numeric columns and need to check and see if you are near the cap for that data type - is this an accurate picture ?|||You got that 100% corrected. :-)

Thanks rnealejr|||So you have 2 options:

1. Search through every table for int/bigints and compare against max value.
2. Create a table of only the columns you need to check.

So which one do you want to do ?|||I can find the IDENT column with this script:

select b.name 'Table Name', a.name 'Column Name'
from syscolumns as a inner join sysobjects as b on a.id = b.id
where b.type = 'u'
and a.status & 0x80 > 0

and I needed to resolve the option#1 you described.

Regards with kindly,

Dam234|||It will not matter whether a column is ident or not - you will be searching for all int/bigint which will include identity columns as well.

Let me see what I can scratch up.|||Portions of this may (or may not) exactly address the issue(s).

However, perhaps some of the following may be helpful (if a bit
repetitive):

1) An Identity column in MS Sql Server 2k may be defined using
any of the following types: (filling the range for a numeric / decimal beginning from - 10^38 +1 to 10^38 - 1 would take a while given 1++ )

[bigint]
[int]
[tinyint]
[numeric]
[decimal]

2) For columns that do use built in DBMS functionality a recommendation for finding out information about candidate
keys, including compound keys is to use exec sp_HelpIndex
@.objname = 'TargetTableName'; as MS Sql Server 2k and earlier
implement unique columns at the DBMS level via indices. Looking
at a table object's indices is a means of identifying columns
that are implemented as such using built in DBMS methods.
(Privately maintained unique columns that do not use built in
DBMS functionality to guarantee unique row values may not
necessarily be identifiable using this approach.)

3) For columns that do not use built in DBMS functionality to
guarantee unique row values writing custom functions / stored
procedures may be necessary. This would include columns that are
not DBMS supported Identity columns per se, that are instead
maintained by user created "identity type" functionality. To
determine if such columns are near the limit for the "custom
data type" would obviously depend not only on the current value
and absolute limit of the underlying "type", but also the
private algorithm itself. (A private algorithm may increment,
decrement etc., by different intervals and may or may not
recycle previously used / deleted values and may or may not be
limited to decimal representations, e.g. hex or greater bases could be used.).

4) An example (to identify candidate keys, including compound
keys that DO use built in DBMS methods):
Use Northwind
Go
exec sp_HelpIndex @.objname = 'Orders'

5) A recommended general means for finding out information about DBMS Identity columns is to use sp_columns, and / or sp_help, and /
or as rnealejr has noted, DBCC CHECKIDENT ('table_name', NORESEED)
and / or IDENT_CURRENT('table_name').

For example:

USE pubs
EXEC sp_help jobs
EXEC sp_columns jobs
EXEC sp_columns @.table_name = 'jobs', @.column_name = 'job_id'
Select IDENT_CURRENT('jobs') As 'IdentCurrent'
Go
DBCC CHECKIDENT ('jobs', NORESEED)

6) More specific means for finding out information about DBMS Identity
columns:

If the additional overhead involved (in the form of maintenance)
is acceptable, one may create user special stored procedures /
functions to provide a variety of additional information about
table objects with identity columns. (Similar to
sp_TableIdentityColumnMetaData and
fn_TableIdentityColumnsMetaData posted here, or
sp_TableIdentityColumns and fn_TableIdentityColumns, posted
earlier in this thread.)

Note: When Sql Server upgrades, service pack, or hot fix changes
alter the underlying system tables involved such user special
stored procedures / functions (that select directly from system
tables) may very well require modification to continue to run
and / or return correct result sets.|||Thanks DBA. I already know how to identify IDENT column, the other
numerical columns are the one that I have to deal with.sql