discover all indexes in one DataBase

Question

discover all indexes in one DataBase

golyra

Valued Member

Points: 73
More actions
March 6, 2006 at 5:57 am

#365455

HI
I would like to have a list of all Indexes already exist in one data base.
I am using Sql Server 2000.
How can I get that?
TIA.
Goly.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

this should give you a list of all indexes and keys...

select table_name, column_name, constraint_name 
from information_schema.key_column_usage
order by table_name

**ASCII stupid question, get a stupid ANSI !!!**

devaraya Default port Points: 1455 More actions · Answer 2

You can try this also.....

select a.name table_name,xtype,b.name index_name from sysobjects a,sysindexes b where a.xtype = 'U' and a.id=b.id order by a.name

Ramaa

golyra Valued Member Points: 73 More actions · Answer 3

the results are only unique keys. what bout thoso indexes which are not unique or are not keys?

Lowell SSC Guru Points: 323546 More actions · Answer 4

hee's something i've been fiddling with; i still have to get hte column names for each index, but this finds all indexes i think; remove the top 100 for all of them, this is a long running query.

when i get the column names right i'll repost with the finished SQL.

select top 100

'CREATE '

+ case when INDEXPROPERTY (I.id,I.name,'IsUnique') = 1 then ' UNIQUE ' ELSE '' END

+ case when INDEXPROPERTY (I.id,I.name,'IsClustered') = 1 then ' CLUSTERED ' ELSE '' END

+ ' INDEX [' + I.name + '] on [' + object_name(I.id) + '] (columnames) ' ,

object_name(I.id) as TableName,

I.Id as TableID,

I.indid as IndexId,

I.name as IndexName,

I.status,

INDEXPROPERTY (I.id,I.name,'IsUnique') as IsUnique,

INDEXPROPERTY (I.id,I.name,'IsClustered') as IsClustered,

INDEXPROPERTY (I.id,I.name,'IndexFillFactor') as IndexFillFactor

from sysindexes I

where I.indid > 0

and I.indid < 255

and (I.status & 64)=0

--only non PK and not Unique: what i call 'normal' indexes

--and INDEXPROPERTY (I.id,I.name,'IsUnique') =0

--and INDEXPROPERTY (I.id,I.name,'IsClustered') =0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

golyra Valued Member Points: 73 More actions · Answer 5

HI

thank you every one for your answers.

Thats the way I got the answer:

SELECT sysindexes.id AS IndexID, sysobjects.name AS TableName, sysindexes.name AS IndexName, sysindexes.indid as [Type]

FROM sysindexes INNER JOIN

sysobjects ON sysindexes.id = sysobjects.id

---------

1 = Clustered index

>1 = Nonclustered

255 = Entry for tables that have text or image data

Lowell SSC Guru Points: 323546 More actions · Answer 6

ok here's an updated version that has all the cREATE INDEX statements in it; i could not figure out how to do it without a cursor, but this is more of a DBA tool than something being used as production code:

SELECT TOP 100

REPLICATE(' ',4000) AS COLNAMES ,

OBJECT_NAME(I.ID) AS TABLENAME,

I.ID AS TABLEID,

I.INDID AS INDEXID,

I.NAME AS INDEXNAME,

I.STATUS,

INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE') AS ISUNIQUE,

INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') AS ISCLUSTERED,

INDEXPROPERTY (I.ID,I.NAME,'INDEXFILLFACTOR') AS INDEXFILLFACTOR

INTO #TMP

FROM SYSINDEXES I

WHERE I.INDID > 0

AND I.INDID < 255

AND (I.STATUS & 64)=0

--uncomment below to eliminate PK or UNIQUE indexes;

--what i call 'normal' indexes

--AND INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE') =0

--AND INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') =0

DECLARE

@ISQL VARCHAR(4000),

@TABLEID INT,

@INDEXID INT,

@MAXTABLELENGTH INT,

@MAXINDEXLENGTH INT

--USED FOR FORMATTING ONLY

SELECT @MAXTABLELENGTH=MAX(LEN(TABLENAME)) FROM #TMP

SELECT @MAXINDEXLENGTH=MAX(LEN(INDEXNAME)) FROM #TMP

DECLARE C1 CURSOR FOR

SELECT TABLEID,INDEXID FROM #TMP

OPEN C1

FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID

WHILE @@FETCH_STATUS <> -1

BEGIN

SET @ISQL = ''

SELECT @ISQL=@ISQL + ISNULL(SYSCOLUMNS.NAME,'') + ',' FROM SYSINDEXES I

INNER JOIN SYSINDEXKEYS ON I.ID=SYSINDEXKEYS.ID AND I.INDID=SYSINDEXKEYS.INDID

INNER JOIN SYSCOLUMNS ON SYSINDEXKEYS.ID=SYSCOLUMNS.ID AND SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID

WHERE I.INDID > 0

AND I.INDID < 255

AND (I.STATUS & 64)=0

AND I.ID=@TABLEID AND I.INDID=@INDEXID

ORDER BY SYSCOLUMNS.COLID

UPDATE #TMP SET COLNAMES=@ISQL WHERE TABLEID=@TABLEID AND INDEXID=@INDEXID

FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID

END

CLOSE C1

DEALLOCATE C1

--AT THIS POINT, THE 'COLNAMES' COLUMN HAS A TRAILING COMMA

UPDATE #TMP SET COLNAMES=LEFT(COLNAMES,LEN(COLNAMES) -1)

SELECT 'CREATE '

+ CASE WHEN ISUNIQUE = 1 THEN ' UNIQUE ' ELSE ' ' END

+ CASE WHEN ISCLUSTERED = 1 THEN ' CLUSTERED ' ELSE ' ' END

+ ' INDEX [' + UPPER(INDEXNAME) + ']'

+ SPACE(@MAXINDEXLENGTH - LEN(INDEXNAME))

+' ON [' + UPPER(TABLENAME) + '] '

+ SPACE(@MAXTABLELENGTH - LEN(TABLENAME))

+ '(' + UPPER(COLNAMES) + ')'

+ CASE WHEN INDEXFILLFACTOR = 0 THEN '' ELSE ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) END --AS SQL

FROM #TMP

--SELECT * FROM #TMP

DROP TABLE #TMP

results:

CREATE UNIQUE INDEX [UQ_SFLNREHAB] ON [SFLNREHAB] (SFHEADTBLKEY,TBREHABTYPETBLKEY)

CREATE UNIQUE CLUSTERED INDEX [PK__MSGLTRAN__067A6141] ON [MSGLTRAN] (MSGLTRANTBLKEY)

CREATE UNIQUE CLUSTERED INDEX [PK__PPPRJSTG__4C81FE7F] ON [PPPRJSTG] (PRJSTAGETBLKEY)

CREATE UNIQUE CLUSTERED INDEX [PK__TBMINOR__59463169] ON [TBMINOR] (MINORITYTBLKEY)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 7

Fatastic script Lowell ...I had the need to write one and 'viola' ... yours was there ... thanks !!!

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

devaraya Default port Points: 1455 More actions · Answer 8

devaraya

Default port

Points: 1455

March 7, 2006 at 9:12 pm

#625314

Hai Lowell,

Very good script keep it up.

Ramaa