Professional Web Applications Themes

Retrieving Coulmns in an Index from System Tables - Microsoft SQL / MS SQL Server

Hello Everyone! I am trying to programmatically derive the columns from syscolumns that constitute any existing index (as defined as existence in the sysindexes system table). Doenation states that the sysindexes.keys column is the "list of the column IDs of the columns that make up the index key." However, this column's datatype is varbinary(816). How can I join back to syscolumns on colid or derive colids from this? Any help is greatly appreciated! Thanks! Allen Shannon...

  1. #1

    Default Retrieving Coulmns in an Index from System Tables

    Hello Everyone!

    I am trying to programmatically derive the columns from
    syscolumns that constitute any existing index (as defined
    as existence in the sysindexes system table).

    Doenation states that the sysindexes.keys column is
    the "list of the column IDs of the columns that make up
    the index key." However, this column's datatype is
    varbinary(816). How can I join back to syscolumns on
    colid or derive colids from this?

    Any help is greatly appreciated!

    Thanks!
    Allen Shannon
    Allen Guest

  2. #2

    Default Re: Retrieving Coulmns in an Index from System Tables

    Well, you can just use sp_helpindex 'tablename' for that. You shouldn't
    attempt to access the system tables directly, since they may disappear or
    change in functionality in future versions... but to really see what's going
    on:

    USE MASTER
    GO
    EXEC sp_helptext sp_helpindex

    Modify the source to suit your needs. But, as per above, do not rely on
    this code in production systems.






    "Allen Shannon" <com> wrote in message
    news:012701c3628a$445bc660$gbl... 


    Aaron Guest

  3. #3

    Default Re: Retrieving Coulmns in an Index from System Tables

    Allen,

    The sp_help stored procedure will return a string with the index column
    names. You might examine that code. Or use its output directly.

    Russell Fields
    "Allen Shannon" <com> wrote in message
    news:012701c3628a$445bc660$gbl... 


    Russell Guest

Similar Threads

  1. Data Query - Error Retrieving Tables
    By Hung Kuen Kung Fu in forum Dreamweaver AppDev
    Replies: 2
    Last Post: March 24th, 03:39 PM
  2. Index-organized tables
    By Pavel in forum Oracle Server
    Replies: 0
    Last Post: August 29th, 08:29 AM
  3. Retrieving Files from another system's hdd
    By Shawn in forum Windows Setup, Administration & Security
    Replies: 0
    Last Post: July 14th, 01:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139