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

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)

About these ads

14 thoughts on “SQL SERVER – Get Information of Index of Tables and Indexed Columns

  1. 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

  2. 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?

  3. 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

  4. 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…………

  5. Hi,

    Thanks for the useful tips.
    I need help to sort out one problem. Actually in our database at one point of time we created an index on a single column and released it in our upgrade script and now some of the clients are running with this. But now according to our requirement we want to update the same index but want it on two columns. But we dont want to drop the index in our upgrade script. Is there any way to retrieve the column names on the existing index so that we would be able to update our upgrade script to check if the existing index is on one column create index on second as well other otherwise create multi column index .

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s