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
    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:
    261
    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.