query on sbspace/blobspace

I have the following requirements: 1) I need to identify all the tables which have columns TEXT/BYTE/CLOB/BLOB 2) I need to find out the columns of type TEXT/BYTE/CLOB/BLOB is using which Blobspace/Sbspace to store data. 3) I should be able to execute an SQL query and get the above information. Any help to solve this problem is highly appreciated. Thanks in advance -pratheep

This Query will tell you what extended type you have used in an Informix database:- SELECT UNIQUE xt.name FROM systables st , syscolumns sc , sysxtdtypes xt WHERE st.tabid = sc.tabid AND sc.extended_id = xt.extended_id AND tabtype = 'T' GROUP BY xt.name ; And this Query will tell you what table.columns are of a type XDTType:- SELECT tabname || '.' || colname FROM systables st , syscolumns sc , sysxtdtypes xt WHERE st.tabid = sc.tabid AND sc.extended_id = xt.extended_id AND tabtype = 'T' AND xt.name = "XDType" ; -- eg: blob, clob, st_point ... Stuart McCann Integrated Spatial Services Unit Information Communication & Technology Department of Lands, Bathurst Phone: (02) 63328284 stuart.mccann@lands.nsw.gov.au -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of PRATHEEP KK Sent: Monday, 29 June 2009 10:29 PM To: ids@iiug.org Subject: query on sbspace/blobspace [16180] I have the following requirements: 1) I need to identify all the tables which have columns TEXT/BYTE/CLOB/BLOB 2) I need to find out the columns of type TEXT/BYTE/CLOB/BLOB is using which Blobspace/Sbspace to store data. 3) I should be able to execute an SQL query and get the above information. Any help to solve this problem is highly appreciated. Thanks in advance -pratheep ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. *************************************************************** This message is intended for the addressee named and may contain confidential information. If you are not the intended recipient, please delete it and notify the sender. Views expressed in this message are those of the individual sender, and are not necessarily the views of the Department of Lands. This email message has been swept by MIMEsweeper for the presence of computer viruses. *************************************************************** Please consider the environment before printing this email.