February 17, 2014 at 9:45 pm
Comments posted to this topic are about the item List Table Foreign Keys, Primary key and indexes
February 18, 2014 at 8:02 am
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
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
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
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
Nice script, thanks.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy