October 10, 2008 at 7:45 am
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
October 10, 2008 at 9:04 am
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 & 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
--------------
WHERE ISCLUSTERED = 0
--------------
--SELECT * FROM #TMP
DROP TABLE #TMP
[/font]
Lowell
October 10, 2008 at 10:00 am
I think something got goofed up in the cut-paste process :
(I.STATUS & 64)
October 19, 2008 at 4:23 pm
Nice Script
Cheers !
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply