January 29, 2007 at 9:50 am
Hello, I have the query below that work great on SQL server 2005. What will be the closest statement to that for SQl server 2000?
Currently I have view that calls a procedure to populate the index columns, but I need something simple like the query below.
Thanks a lot, mj
SQL server 2005:
SELECT sys.objects.name as 'Table Name', sys.indexes.name as 'Index Name',
sys.columns.name as 'Column Name', sys.index_columns.key_ordinal as 'Position'
FROM sys.objects
INNER JOIN sys.indexes ON sys.objects.object_id = sys.indexes.object_id
INNER JOIN sys.index_columns ON sys.indexes.object_id = sys.index_columns.object_id
AND sys.indexes.index_id = sys.index_columns.index_id
INNER JOIN sys.columns ON sys.index_columns.object_id = sys.columns.object_id
AND sys.index_columns.column_id = sys.columns.column_id
WHERE sys.objects.type = 'U'
January 29, 2007 at 10:02 am
not 100% sure that this is what you want, but this is pretty close: limiting to top 100 because this can be a whopper of a resultset.
| Table Name | Index Name | Column Name | Position |
| sysobjects | sysobjects | id | 1 |
| sysobjects | ncsysobjects | name | 1 |
| sysproperties | sysproperties | id | 1 |
| sysproperties | sysproperties | type | 2 |
| sysproperties | sysproperties | smallid | 3 |
| sysproperties | sysproperties | name | 4 |
| sysdepends | sysdepends | deptype | 1 |
| sysdepends | sysdepends | depid | 2 |
| sysdepends | sysdepends | id | 3 |
| sysdepends | sysdepends | depnumber | 4 |
| sysdepends | sysdepends | number | 5 |
SELECT TOP 100
OBJECT_NAME(I.ID) AS 'Table Name',
I.NAME AS 'Index Name',
syscolumns.name as 'Column Name',
SYSINDEXKEYS.keyno as 'Position'
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
order by I.NAME ,SYSINDEXKEYS.keyno
Lowell
January 29, 2007 at 10:42 am
Thanks a lot for the help. I had a bad join between the syscolumns and and the indkeys.
Thanks again, mj
January 29, 2007 at 11:42 am
What will be the way to select the column names as a string in the order they appear in the index?
I tried but with no success...
I need a rust set like this:
table_name, index_name, column_list
sysproperties, sysproperties, id+ type+smallid+name
from the previous example.
Thanks a lot for the help.
mj
January 30, 2007 at 6:18 am
I had previously written a script that does exactly that...build sthe commands: try this out:
| Results: |
| CREATE UNIQUE INDEX [UQPRJEXC] ON [GMPRJEXC] (PRJTBLKEY,GMSFEXCTBLKEY) WITH FILLFACTOR = 90 |
| CREATE UNIQUE CLUSTERED INDEX [PK__TBACCTTY__04AFB25B] ON [TBACCTTY] (ACCOUNT_TYPE_ID) WITH FILLFACTOR = 90 |
| CREATE UNIQUE CLUSTERED INDEX [PK__TRCDWNLDDET__04C722F9] ON [TRCDWNLDDET] (TRCDWNLDDETTBLKEY) WITH FILLFACTOR = 90 |
| CREATE INDEX [TRCDWNLDDET_PROCIDNDX] ON [TRCDWNLDDET] (PROCESSINGID) WITH FILLFACTOR = 90 |
| CREATE UNIQUE CLUSTERED INDEX [PK__PPRANONS8__04F20B82] ON [PPRANONS8] (NONSEC8TBLKEY) |
| CREATE UNIQUE CLUSTERED INDEX [PK__TBTRACKC__6E814571] ON [TBTRACKC] (TRACKINGTBLKEY) WITH FILLFACTOR = 90 |
script:
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
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply