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 (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – T-SQL Script to Find Details About TempDB Information
Next Post
SQLAuthority News – RML Utilities for SQL Server

Related Posts

14 Comments. Leave new

  • Mohammad Hameed
    December 20, 2007 8:02 pm

    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

    Reply
  • Mohammad Hameed
    December 20, 2007 8:23 pm

    Hi,

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

    Thanks again

    Mohammad

    Reply
  • 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?

    Reply
  • Really useful!!!
    Thx!!!

    Reply
  • send me some tips and material to my mail please

    Reply
  • Shivam Shukla
    March 1, 2011 12:07 am

    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

    Reply
  • 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…………

    Reply
  • I have (Is Identity) = Yes for a column – ID

    Now, I want to know which next value will be inserted for this column.

    Can I know that?

    Thank u in advance.

    Reply
  • 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 .

    Reply

Leave a Reply

Menu