SQL SERVER – Get Information of Index of Tables and Indexed Columns

« SQL SERVER – T-SQL Script to Find Details About TempDB

SQLAuthority News – RML Utilities for SQL Server »

SQL SERVER – Get Information of Index of Tables and Indexed Columns

December 18, 2007 by pinaldave

Knowledge of T-SQL inbuilt functions and store procedure can save great amount of time for developers. Following is very simple store procedure which can display name of Indexes and the columns on which indexes are created. Very handy stored Procedure.
USE AdventureWorks; GO EXEC sp_helpindex 'Person.Address' GO
Above SP will return following information.
IndexName – IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
Index_Description – nonclustered, unique located on PRIMARY
Index_Keys – AddressLine1, AddressLine2, City, StateProvinceID, PostalCode

Let me know if you think this kind of small tips are useful to you.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Index, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 9 Comments

9 Responses

on December 20, 2007 at 8:02 pm | Reply Mohammad Hameed

Hi,

Yes these king of small tips are helpful. I have a question of my own though it has to do with system tables containing information about user defined tables.

If I want to find the number of columns in a user defined table, can i do it with a simple select statement, or do i have to manually go in each table and count it?

I hope you will answer this question at the earliest possible

Regards

Mohammad
on December 20, 2007 at 8:23 pm | Reply Mohammad Hameed

Hi,

Sorry, I should have checked other articles before posting my question.

Thanks again

Mohammad
on January 9, 2008 at 4:46 pm | Reply abhishek

Table:

TableA

Columns:

Column1

Column2

Column3

Column4

Indexes

Column1 – Simple individual Index

Column2 – Simple individual Index

Query

1. SELECT * FROM TABLEA WHERE COLUMN1=’A’ AND COLUMN2=’B’

2. SELECT * FROM TABLEA WHERE COLUMN2=’B’ AND COLUMN1=’A’

3. SELECT * FROM TABLEA WHERE COLUMN1=’A’ AND COLUMN3=’B’

4. SELECT * FROM TABLEA WHERE COLUMN2=’A’ AND COLUMN4=’B’

could you pleasetell me which indexes use in case (queries.)
or both are equally important.?

is the other individual index useless and should be deleted?
on July 28, 2008 at 3:00 pm | Reply Anima003

Really useful!!!
Thx!!!
on December 3, 2010 at 9:20 am | Reply sekhar

send me some tips and material to my mail please
on March 1, 2011 at 12:07 am | Reply Shivam Shukla

Hello Sir,

please answer me that how can i get the -ve value’s sum in other column like as given below table

id value diffrence

1 900 900
2 -500 400
3 -200 200

Please tell me the query of sql server 2005.
it will b great full.

[email removed]
mail me plz
- on March 3, 2011 at 2:03 pm | Reply madhivanan
  
  Search for Running Total in this site
on July 4, 2011 at 9:51 am | Reply Naveed

dear
Every one
to creart an index we write this quary
CREATE INDEX index_name
ON table_name (column_name)

but if you search values on it, so for this you write quary…………
- on July 4, 2011 at 4:24 pm | Reply madhivanan
  
  You have to make sure that functions are not used over this column in the where clause