Post reply

identifying Identification fields using system tables (sqlserver2000)

  • July 16, 2004 at 11:41 am

    #161686

    I am working on a set of SQL Server utility stored procedures that will speed up our standard database and table documentation process.  Currently we have to import the database into access and run the documentor on it, which takes lots of time and ends up screwing up the data type displays. 

    My question is:  How do you distinguish ID fields by using the system tables?

    Thanks!

    Josh

  • July 17, 2004 at 1:57 pm

    #514983

    SELECT

     so.name + '.' + sc.name AS column_name

    FROM

     sysobjects so

     INNER JOIN syscolumns sc ON so.id = sc.id

      AND COLUMNPROPERTY(OBJECT_ID(so.name),sc.name,'IsIdentity') = 1

     

     

    Derrick Leggett
    Mean Old DBA
    When life gives you a lemon, fire the DBA.

  • July 17, 2004 at 6:16 pm

    #514991

    Hi,

    If You want something like this ( for free ):

    http://www.ngallery.org/dbdocumenter/demo/

    You can go here:

    http://www.ericjsmith.net/codesmith/

    And use this template:

    http://www.ngallery.org/dbdocumenter/

    It works. ( 3 hours to download and learn.. )

    /rockmoose


    You must unlearn what You have learnt

  • July 17, 2004 at 6:16 pm

    #514990

    Very undocumented...

    select * from syscolumns where colstat & 1 = 1

    Will give the Identity columns

    /rockmoose


    You must unlearn what You have learnt

  • July 17, 2004 at 6:27 pm

    #514992

    By the way.. search the scripts on this site:

    People say good thigs about this one:

    http://qa.sqlservercentral.com/scripts/contributions/1005.asp

    ( sorry for all the messy posts, maybe they will help )

    /rockmoose


    You must unlearn what You have learnt

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply