April 20, 2009 at 5:17 pm
I need a script that will list all indexes in a database that SHOULD be defined as UNIQUE (based on the data distribution) but are not.
Does anyone have such a script?
Thanks!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 20, 2009 at 8:46 pm
Marios Philippopoulos (4/20/2009)
I need a script that will list all indexes in a database that SHOULD be defined as UNIQUE (based on the data distribution) but are not.Does anyone have such a script?
Thanks!
OK here's my first guess at it; first I've created a temp table that has all my indexes that are NOT unique in a database, and stuck their table names and column names in that table.
I'm being lazy and re-using an existing script that already did the grunt work.
next i built a GROUP BY ...HAVING COUNT(*) 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 where isunique=0
--DROP TABLE #TMP
--SELECT * FROM #TMP where isunique=0
[/code]
and no select the results for queries to analyze:
SELECT 'SELECT ' + COLNAMES + ' FROM ' + TABLENAME + ' GROUP BY ' + COLNAMES + ' HAVING COUNT(*) <=1'
FROM #TMP where isunique = 0
Lowell
April 20, 2009 at 8:51 pm
hmmm... thinking about it, i think you have to test the count > 1 and EXCLUDE those indexes istead;
a table with no rows simply will not return results in the group by...
exclude indexes that matched this criteria:
SELECT 'SELECT ' + COLNAMES + ' FROM ' + TABLENAME + ' GROUP BY ' + COLNAMES + ' HAVING COUNT(*) > 1'
FROM #TMP where isunique = 0
Lowell
April 21, 2009 at 1:50 pm
Thank you for the help, I will try your script.
Much appreciated!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply