• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • Last Modified:

index - cluster / noncluster

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

thanks

0
royjayd
Asked:
royjayd
2 Solutions
 
elimesikaCommented:
see attached code

for your 2nd question:

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.

Therefor there can be only one clustered index
SELECT 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'  and
OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) = 'myproject' 
ORDER BY T.[name], I.[index_id], IC.[key_ordinal]

Open in new window

0
 
Alpesh PatelAssistant ConsultantCommented:
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' and OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) = 'dbo'  ORDER BY T.[name], I.[index_id], IC.[key_ordinal]  
0
 
royjaydAuthor Commented:
elimesika:

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?

thx
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now