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_id IDX.name IDX.type_desc IDX.is_primary_key IDX.index_column_id IDX.column_id IDX.column_name IDX.key_ordinal
1 PK__Reader__7FCE2F09 CLUSTERED 1 1 1 DeviceID 1
1 PK__Reader__7FCE2F09 CLUSTERED 1 1 8 EgressAreaID 1
2 XIF4Reader NONCLUSTERED 0 1 8 EgressAreaID 1
2 XIF4Reader NONCLUSTERED 0 1 1 DeviceID 1
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply