Post reply

Finding Tables without primary keys and clustered indexes.

  • September 4, 2008 at 2:27 am

    #125160

    Hello,

    I am hoping one of you wizards would help me out with what I expect is a bit of an old chesnut.

    I need to find all the tables without primary keys, and tables without clustered indexes. I have of course googled this and found a few examples including a couple from this site, but they do not work when I try them. I am quite new to t-sql but gradually picking it up, I just dont know how to find this out on my own.

    Thanks for any help, I have sql 2005 Ent with the latest service pack.

    Kind regards,

    P.

  • September 4, 2008 at 2:51 am

    #866998

    Primary key:

    SELECT name from sys.tables where objectproperty(object_id,'TableHasPrimaryKey') = 0

    Clustered index:

    SELECT name from sys.tables where objectproperty(object_id,'TableHasClustIndex') = 0

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 4, 2008 at 3:23 am

    #867011

    'to run this query ,to get result of non using primary key or cluster index tables

    SELECT name ,case when objectproperty(object_id,'TableHasPrimaryKey') =0 then 'No' else 'Yes' end IsPrimaryKey,

    case when objectproperty(object_id,'TableHasClustIndex') =0 then 'No' else 'Yes' end IsClustIndex

    from sys.tables where objectproperty(object_id,'TableHasPrimaryKey') =0 or objectproperty(object_id,'TableHasClustIndex') = 0

  • September 4, 2008 at 4:17 am

    #867026

    Hello,

    Thank you both for replying, I got what was needed from them.

    Cheers,

    P.

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

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