Re: Checking columname for index
This is a multi-part message in MIME format.
--------------13D57257EEE2B7A02E1BFC1F
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
albertrocker@my-deja.com wrote:
>
> Hello,
>
> I am maintaining some SPL, which contains a CREATE INDEX statement.
>
> I am taking this opportunity to make the procedure a little more
> robust, by checking that the column in question is not already indexed
> before issuing the 'CREATE'
>
> The system catalogue does not seem to provide much help, as I can't
> link sysindexes with syscolumns.
>
> Does anyone have any SQL or ideas that may be able to help me in this?
I have attached a mildly useful script for creating a slightly (only
slightly though) more normalised view of the sysindexes table. It does
allow you to do things like:
SELECT tabname, idxname, partno, colname
FROM sysindexes2 i, systables t, syscolumns c
WHERE i.tabid = t.tabid
AND i.tabid = c.tabid
AND i.colno = c.colno
--AND idxname[1,1] != " " -- Don't show system generated constraints
ORDER BY idxname, partno
Or whatever takes your fancy. I guess you need something like:
SELECT idxname
FROM sysindexes2 i, systables t, syscolumns c
WHERE i.tabid = t.tabid
AND i.tabid = c.tabid
AND i.colno = c.colno
AND tabname = "<my table>"
AND colname = "<my column>"
AND partno = 1
Not tested of course, but you get the idea.
Cheers,
--
Mark.
+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /|
| http://www.informix.com http://www.informixhandbook.com |///// / //|
| http://www.iiug.org +-----------------------------------+//// / ///|
| |What year 2000 bug? year 2000 bug? |/// / ////|
| |year 2000 bug? year 2000 bug? year |// / /////|
| |2000 bug? year 2000 bug? year 1900 |/ ////////|
+----------------------+-----------------------------------+-----------+
--------------13D57257EEE2B7A02E1BFC1F
Content-Type: text/plain; charset=us-ascii;
name="sysindexes2.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="sysindexes2.sql"
{
Copyright (c) Mydas Solutions 2000 All Rights Reserved.
sysindexes2.sql
29/03/2000
Mark D. Stock
View on sysindexes which simplifies joins to syscolumns
}
--------------------------------------------------------------------------------
CREATE VIEW sysindexes2 (idxname, owner, tabid, idxtype, clustered, colno,
levels, leaves, nunique, clust, partno)AS
SELECT idxname, owner, tabid, idxtype, clustered, part1,
levels, leaves, nunique, clust, 1
FROM sysindexes
UNION ALL
SELECT idxname, owner, tabid, idxtype, clustered, part2,
levels, leaves, nunique, clust, 2
FROM sysindexes
UNION ALL
SELECT idxname, owner, tabid, idxtype, clustered, part3,
levels, leaves, nunique, clust, 3
FROM sysindexes
UNION ALL
SELECT idxname, owner, tabid, idxtype, clustered, part4,
levels, leaves, nunique, clust, 4
FROM sysindexes
UNION ALL
SELECT idxname, owner, tabid, idxtype, clustered, part5,
levels, leaves, nunique, clust, 5
FROM sysindexes
UNION ALL
SELECT idxname, owner, tabid, idxtype, clustered, part6,
levels, leaves, nunique, clust, 6
FROM sysindexes
UNION ALL
SELECT idxname, owner, tabid, idxtype, clustered, part7,
levels, leaves, nunique, clust, 7
FROM sysindexes
UNION ALL
SELECT idxname, owner, tabid, idxtype, clustered, part8,
levels, leaves, nunique, clust, 8
FROM sysindexes
UNION ALL
SELECT idxname, owner, tabid, idxtype, clustered, part9,
levels, leaves, nunique, clust, 9
FROM sysindexes
UNION ALL
SELECT idxname, owner, tabid, idxtype, clustered, part10,
levels, leaves, nunique, clust, 10
FROM sysindexes
UNION ALL
SELECT idxname, owner, tabid, idxtype, clustered, part11,
levels, leaves, nunique, clust, 11
FROM sysindexes
UNION ALL
SELECT idxname, owner, tabid, idxtype, clustered, part12,
levels, leaves, nunique, clust, 12
FROM sysindexes
UNION ALL
SELECT idxname, owner, tabid, idxtype, clustered, part13,
levels, leaves, nunique, clust, 13
FROM sysindexes
UNION ALL
SELECT idxname, owner, tabid, idxtype, clustered, part14,
levels, leaves, nunique, clust, 14
FROM sysindexes
UNION ALL
SELECT idxname, owner, tabid, idxtype, clustered, part15,
levels, leaves, nunique, clust, 15
FROM sysindexes
UNION ALL
SELECT idxname, owner, tabid, idxtype, clustered, part16,
levels, leaves, nunique, clust, 16
FROM sysindexes
--------------------------------------------------------------------------------
--------------13D57257EEE2B7A02E1BFC1F--