September 11, 2007 at 4:36 am
Here is an example:
create
table testing (fid int, ftest varchar(max))
create index idx_tmp on testing (fid) include (ftest)
sp_helpindex
testing
output
:
index_name index_description index_keys
idx_tmp nonclustered located on PRIMARY fid
The question: Where can I see that ftest was also included in the index ?
Thank
R
September 11, 2007 at 4:51 am
Unfortunately sp_helpindex does not contain this information. You have to dig this out from the system views. The ones you will need are sys.indexes and sys.index_columns. The later has a is_included_column.
Regards,
Andras
September 11, 2007 at 4:54 am
A simple example:
SELECT sts.name
, st.name
, si.name
, ssc.name
, is_included_column
FROM sys.tables AS st
JOIN sys.schemas AS sts ON sts.schema_id = st.schema_id
JOIN sys.indexes AS si ON st.object_id = si.object_id
JOIN sys.index_columns AS sc ON si.object_id = sc.object_id
AND si.index_id = sc.index_id
JOIN sys.columns AS ssc ON si.object_id = ssc.object_id
AND sc.column_id = ssc.column_id
ORDER BY st.object_id
, si.index_id
, sc.column_id
Andras
September 11, 2007 at 4:57 am
Got it , thank you.
R
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply