Post reply

List Table Foreign Keys, Primary key and indexes

  • February 17, 2014 at 9:45 pm

    #289635

    Comments posted to this topic are about the item List Table Foreign Keys, Primary key and indexes

  • February 18, 2014 at 8:02 am

    #1689923

    Results for "index"portion are not reliable - they show multiple columns. Example:

    table Reader with the following columns:

    DeviceID, DoorTypeID, ReaderTypeID, DegradedMode, KeypadPresent, AntipassbackTime, GuestSignout, EgressAreaID, GuestSignin, AutoRelock, HasAssociatedOtisDEC, OEInterfaceID, OtisDECHostname, OEOperationModeID, ReaderModel, FirmwareVersion, EvaluationNumber

    and two indexes:

    XIF4Reader - [EgressAreaID] ASC

    PK__Reader__7FCE2F09 - DeviceID (from PK)

    is listed as:

    IDX.index_idIDX.nameIDX.type_descIDX.is_primary_keyIDX.index_column_idIDX.column_idIDX.column_nameIDX.key_ordinal

    1PK__Reader__7FCE2F09CLUSTERED111DeviceID1

    1PK__Reader__7FCE2F09CLUSTERED118EgressAreaID1

    2XIF4ReaderNONCLUSTERED018EgressAreaID1

    2XIF4ReaderNONCLUSTERED011DeviceID1

  • February 18, 2014 at 12:46 pm

    #1690025

    For the index query try

    INNER JOIN sys.index_columns b

    ON a.[object_id] = b.[object_id] and a.index_id = b.index_id

  • February 20, 2014 at 4:20 pm

    #1690925

    Or, you could just run sp_help <tablename> and get all this information without any additional coding.

    Note that you cannot qualify your table name with a schema name. "sp_help dbo.MyTable" will not work, but "sp_help MyTable" will work.

    Also, sp_help will only look for an object in the current database.

  • February 18, 2015 at 8:23 am

    #1778367

    I always appreciate seeing the underlying queries for stored procedures

    the script here offers more flexibility then the sp_help function

    Thanks!

  • May 5, 2015 at 3:12 pm

    #1795768

    Nice script, thanks.

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

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