SQL Script to find Indexed columns in a table / database

Leave a comment (3) Go to comments

Recently, one of my customer has asked me a report, to know what all columns has index created on them. I used the following query to fulfill this requirement.

-- ******************************************** */
-- Description                                  */
-- ============                                 */
-- Script to find Tables which has index and will also show you on which columns the indes resides.
-- This Script will help you to find indexed columns.
-- =============================================*/
-- Compatibility  :     2000+                   */
-- ******************************************** */

--EXEC dbo.ColumnsIndexed

create PROCEDURE dbo.ColumnsIndexed
AS
SET NOCOUNT ON 
DECLARE     @sTableName         SYSNAME
DECLARE     @Tablename         VARCHAR(50)
DECLARE     @sSQL             VARCHAR(50)
DECLARE     @iRowCount          INT
DECLARE     @t_TableNames_Temp     TABLE
                    (table_name SYSNAME)

If exists (select object_name(id) from sysobjects where name='tblResults')
DROP TABLE tblResults
-- Create the temporary table...
create TABLE tblResults
(
   [name]   nvarchar(50),
   status   int,
   indid    int,
   OrigFillFactor int,
   IndCol1  nvarchar(20),
IndCol2 nvarchar(20),
IndCol3 nvarchar(20),
IndCol4 nvarchar(20), 
IndCol5 nvarchar(20),
IndCol6 nvarchar(20),
IndCol7 nvarchar(20),
IndCol8 nvarchar(20),
IndCol9 nvarchar(20),
IndCol10 nvarchar(20),
IndCol11 nvarchar(20),
IndCol12 nvarchar(20),
IndCol13 nvarchar(20),
IndCol14 nvarchar(20),
IndCol15 nvarchar(20), 
IndCol16 nvarchar(20),
SegName nvarchar(20),
FullTextKey int,
Descending int,
Computed int ,
IsTable int 
)


-- Populate the temp table...
INSERT         @t_TableNames_Temp
select distinct sysobjects.name from sysobjects join sysindexes on sysindexes.id=sysobjects.id
and sysindexes.indid<>0
and xtype  in ('U')
order by sysobjects.name 

SELECT         @iRowCount = COUNT(*) FROM @t_TableNames_Temp
WHILE         @iRowCount > 0  
BEGIN
        Select     @tablename = rtrim(table_name) from @t_TableNames_Temp
        --PRINT @tablename
        INSERT INTO tblResults
                         (name,status,indid,OrigFillFactor,IndCol1,IndCol2,IndCol3,IndCol4,IndCol5,IndCol6,IndCol7,IndCol8,IndCol9,IndCol10,IndCol11,
                      IndCol12,IndCol13,IndCol14,IndCol15,IndCol16,SegName,FullTextKey,Descending,Computed,IsTable)
        Exec ('SP_MSHelpindex ' +  @tablename)

        DELETE FROM @t_TableNames_Temp WHERE @tablename = table_name
        SELECT @iRowCount = @iRowCount - 1
END
    
--RETURN 0
SET NOCOUNT OFF

-- Return the results...
--select * from tblresults
select distinct object_name(id) as Table_name ,tblResults.OrigFillFactor,IndCol1,IndCol2,IndCol3,IndCol4,IndCol5,IndCol6,IndCol7,IndCol8,IndCol9,IndCol10,IndCol11,
                      IndCol12,IndCol13,IndCol14,IndCol15,IndCol16
from sysindexes JOIN tblResults on
sysindexes.name=tblResults.name 
and tblResults.name not like '%_WA_%'
go
EXEC dbo.ColumnsIndexed

OUTPUT


SQL Script to find Indexed columns in a table / database

EOF - SQL Script to find Indexed columns in a table / database, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

3 Comments.

  1. Small correction: If the table name has spaces, it raises an error.

    Change to
    Exec (‘SP_MSHelpindex [' + @tablename +'] ‘)

    Thanks

    Vankayala

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.