Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Nov 2002
    Posts
    6

    Question Unanswered: 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.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  3. #3
    Join Date
    Oct 2002
    Posts
    369

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

    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

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  5. #5
    Join Date
    Oct 2002
    Posts
    369

    Post

    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).
    Attached Files Attached Files

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578

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

    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.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  7. #7
    Join Date
    Oct 2002
    Posts
    369

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

    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

  8. #8
    Join Date
    Nov 2002
    Posts
    6

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

    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!

  9. #9
    Join Date
    Feb 2002
    Posts
    2,232
    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 ?

  10. #10
    Join Date
    Nov 2002
    Posts
    6

    Post

    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

  11. #11
    Join Date
    Feb 2002
    Posts
    2,232
    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 ?

  12. #12
    Join Date
    Feb 2002
    Posts
    2,232
    You can use the Information_Schema.columns view. Look under the DATA_TYPE column.

  13. #13
    Join Date
    Nov 2002
    Posts
    6

    Post

    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

  14. #14
    Join Date
    Nov 2002
    Posts
    6

    Unhappy

    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.

  15. #15
    Join Date
    Oct 2002
    Posts
    369

    Arrow

    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'
    Last edited by DBA; 12-06-02 at 13:34.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •