Listing Indexes

Question

Post reply

Listing Indexes

Buxton69-502449

SSCrazy

Points: 2026
More actions
October 10, 2008 at 7:45 am

#194480

Hope someone can help.

I'm querying sysobjects to retrieve all index information so I can generate a big drop script. (I can generate the create script from scripting out the database in Enterprise Manager but the script to drop the indexes from this just drops the tables to get rid of them which I don't want!)

I can identify all of the constraints from sysindexes and sysobjects:

select sy1.[name], sy2.[name], sy2.xtype from sysobjects as sy1

inner join sysobjects as sy2 on sy1.id=sy2.parent_obj

However, all non key clustered and non clustered indexes are not in sysobjects and only in sysindexes, I can identify all clustered indexes in sysindexes as they all have an indid = 1 and I can match up to my other results to see what I haven't got but I cannot identify all the non key non clustered indexes as the indid is >1 and this brings back everything else in the table.

How can I identify these non key non clustered indexes using the system tables?

Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

i've got this saved in my snippets of code; i added the bolded where statement to ignore clustered indexes:



[font="Courier New"]

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 &amp; 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 &amp; 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



--------------

WHERE ISCLUSTERED = 0


--------------

--SELECT * FROM #TMP

DROP TABLE #TMP

[/font]

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!