Post reply

Primary Key Column

  • July 5, 2006 at 3:55 am

    #171509

    Hi,

    I want to retrieve the columns of the all tables in a particular database which contains "Primary Key" using a select query

    Not By Executing a system procedure Sp_helpconstraint

    I am waiting for ypur valuable suggestions.

    Regards,

    Amit Gupta

     

  • July 5, 2006 at 4:02 am

    #647447

    select * from mydb..syscolumns where status & 0x80 = 0x80

    or

    sp_msforeachdb 'select * from ?.dbo.syscolumns where status & 0x80 = 0x80'

    N 56°04'39.16"
    E 12°55'05.25"

  • July 5, 2006 at 4:10 am

    #647452

    Thanks peter !!

    It's  working fine peter but i want to know ABOUT those cloumns also taht contains a UNIQUE CLUSTERED INDEX ON columns

    Thanks & Regards,

    Amit Gupta

     

  • July 5, 2006 at 4:54 am

    #647465

    Hi Amit,

    Unfortunately this isn't going to be as trivial as running a single query because indexes (and primary keys) can span multiple columns.

    So what you'd need to do is get the objects that are of type 'PK' or 'UQ' and then iteratively determine which columns form a part of those indexes.

    --here's some code to get you started.

    declare constraint_cursor

    for

    select id, xtype, name

    from sysobjects

    where xtype in ('PK','UQ')

    order by parent_obj

    for read only

    --open cursor

    --fetch from cursor

    while @@fetch_status = 0

    begin

    declare @thiskey nvarchar(133) -- 128+5

    declare @i int

    select @thiskey = index_col(@object_name, @indid, 1), @i=2

    select @thiskey

    while (@thiskey is not null)

    begin

    select @thiskey = index_col(@object_name, @indid, @i), @i = @i + 1

    select @thiskey

    end

    --fetch from cursor

    end

    --close and deallocate cursor

    Hope that helps,

    Karl
    source control for SQL Server

  • July 5, 2006 at 6:45 am

    #647482

    Hi,

    I not able to get the desired result form the cursor!!.Is there any way to get the result.

    Regards ,

    Amit Gupta

     

  • July 5, 2006 at 7:27 am

    #647497

    Perhaps you could explain a bit more about what you really want..?

    (a written example would be nice)

    Note that 'Primary Key' and 'Unique Clustered Index' has nothing to do with eachother per se.

    'Primary Key' is an abstract concept (meaning we have decided upon certain rules of behaviour), while a 'Unique Clustered Index' is a physical structure that also has certain rules of behaviour, but there's no inherent connection between the two.

    An intended 'Primary Key' may exist on a table by either of the following: (since it's abstract and exists solely due to the designers choice)

    1) as a declared constraint PRIMARY KEY

    2) as a declared UNIQUE CONSTRAINT

    3) as a UNIQUE INDEX (be it clustered or nonclustered, it doesn't matter)

    4) as nothing special declared or indexed at all...

    All of the above variations may be real-world implementations of a PK (2-4 is common in 3rd party apps )

    though 1) is the one we would like to have always. Problem is that sometimes it's one of the others instead, and we still have to find out which the 'real' intended PK is...

    /Kenneth

     

  • July 5, 2006 at 8:50 am

    #647517

    /*this is designed for v8, but written on v9.
    I'm fairly certain it's v8-compatible.*/
    --

    select

    u.name tabowner

    ,

    ot.name tabname

    ,

    i.name indname

    --use the status bitmap to determine whether the index enforces a PK

    ,

    sign(i.status & 16) IsClustered

    --, indexproperty(i.id, i.name, 'IsClustered') IsClustered

    ,

    sign(i.status & 2048) IsForPK

    ,

    sign(i.status & 2) IsUnique

    --, indexproperty(i.id, i.name, 'IsUnique') IsUnique

    ,

    c.name colname

    ,

    ik.keyno colorder

    from

    sysindexes i

    join

    sysindexkeys ik

        on i.id = ik.id
        and i.indid = ik.indid

    join

    sysobjects ot

        on ot.id = i.id

    join

    sysusers u

        on ot.uid = u.uid

    join

    syscolumns c

        on c.id = ik.id
        and c.colid = ik.colid

    where

    i.status & 2 = 2

    --where indexproperty(i.id, i.name, 'IsUnique') = 1
    /*INDEXPROPERTY() is MS-recommended because more stable
    than decoding the status bitmap
    (which is undocumented therefore potentially changeable),
    but INDEXPROPERTY() doesn't support an IsForPK property.*/

    order

    by u.name

    ,

    ot.name

    ,

    i.name

    ,

    ik.keyno

    --
    /*according to microsoft you should use information_schema views but they are
    often inadequate and are designed primarily for display rather than programmability.
    This example doesn't tell you all indexes, only those which enforce a constraint
    and whose columns are therefore capable of being referenced in a FOREIGN KEY constraint.
    It also doesn't tell you whether an index is clustered.*/
    --

    select

    tc.table_catalog

    ,

    tc.table_schema

    ,

    tc.table_name

    ,

    kcu.constraint_name

    ,

    constraint_type

    ,

    kcu.column_name

    ,

    kcu.ordinal_position

    ,

    kcu.*

    from

    information_schema.key_column_usage kcu

    join

    information_schema.table_constraints tc

        on tc.constraint_catalog = kcu.constraint_catalog
        and tc.constraint_schema = kcu.constraint_schema
        and tc.constraint_name = kcu.constraint_name

    where

    constraint_type in ('PRIMARY KEY','UNIQUE')

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

Viewing 7 posts - 1 through 6 (of 6 total)

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