We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now


index - cluster / noncluster

royjayd asked
Medium Priority
Last Modified: 2012-05-11
hi guys

I want to find out which columns are indexed and which are not in my tables and i am using
this sql (which i found on internet :-0)

SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],      T.[name] AS [table_name], I.[name] AS [index_name], AC.[name] AS [column_name],      I.[type_desc], I.[is_unique], I.[data_space_id], I.[ignore_dup_key], I.[is_primary_key],     I.[is_unique_constraint], I.[fill_factor],    I.[is_padded], I.[is_disabled], I.[is_hypothetical],     I.[allow_row_locks], I.[allow_page_locks], IC.[is_descending_key], IC.[is_included_column]   FROM sys.[tables] AS T      INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]      INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]     INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]   WHERE T.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP'   ORDER BY T.[name], I.[index_id], IC.[key_ordinal]  

which prints index names along with schema names. Is there anyway i  can modify to
show indexes only with schema 'myproject' ? currently it shows all schemas.

Also the result returns  clustered and nonclustered index ? what does that mean?

any ideas appreciated


Watch Question

Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview



thanks for response  and sorry for replying late. i have a question on you if you dont mind.
you said:
>A clustered index indicates that record are physically ordered in that order in the disk.
>A non-clustered index is a data structure that points to the values in the defined order.

can you show by a simple example what you mean by these two statements?

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.