1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Where Identity SQL Server 2008 save?

Discussion in 'Other Languages' started by onlove_ol, May 22, 2011.

  1. onlove_ol

    onlove_ol Junior Member

    Joined:
    Dec 6, 2010
    Messages:
    125
    Likes Received:
    69
    Location:
    Vietnam
    Home Page:
    I new use SQL Server 2008

    I see all PK, UK record now will save in System Tables - Keys (Indexes, Statistics) but i not see Identity record

    Where is it? Can you help me?

    Thanks!
     
  2. 195471

    195471 Regular Member

    Joined:
    Oct 11, 2008
    Messages:
    417
    Likes Received:
    260
    You're not supposed to be seeing "Identity" in the System Tables folder. :confused:

    I'm not sure if this is what you're after, but when you set a primary key in SQL Server Management Studio, there's an option to select an Identity:

    Column Properties > Identity Specification > (Is Identity) > [select "Yes"]
     
    • Thanks Thanks x 1
  3. pcboy

    pcboy Registered Member

    Joined:
    Feb 20, 2009
    Messages:
    60
    Likes Received:
    53
    Location:
    Largest democracy

    System tables wont have any identity column, only user tables can have identity column if you explicitly define it.

    Code:
    SELECT SCHEMA_NAME( OBJECTPROPERTY( OBJECT_ID, 'SCHEMAID' )) AS  SCHEMA_NAME,
          OBJECT_NAME( OBJECT_ID ) AS TABLE,
          NAME AS COLUMN
    FROM  SYS.COLUMNS
    WHERE [B]COLUMNPROPERTY(OBJECT_ID, NAME, 'IsIdentity') = 1[/B]
    will give details about identity column in the database you run the query
     
    • Thanks Thanks x 1
  4. pcboy

    pcboy Registered Member

    Joined:
    Feb 20, 2009
    Messages:
    60
    Likes Received:
    53
    Location:
    Largest democracy
    also you cant directly add/delete/modify data in system tables from 2005 version. From 2005 we cant view metadata,(system tables are hidden from 2005) instead we to query system views,sp

    For eg when you create a job or delete a database, it will automatically update system tables is system databases.