# Select Needed to Retrieve SuperDescriptor and its column components from CONNX **URL:** \ **Category:** Forum\ **Tags:** Adabas-Natural, Adabas, Adabas-Add-on-Products, SQL-Access, Mainframe-Integration, CONNX\ **Created:** [July 24, 2014, 4:27pm UTC](https://techcommunity.softwareag.com/t/select-needed-to-retrieve-superdescriptor-and-its-column-components-from-connx/177680 "2014-07-24T16:27:50Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Min\_Chai](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@Min\_Chai](https://techcommunity.softwareag.com/u/Min_Chai)\ **Post date:** [July 24, 2014, 4:27pm UTC](https://techcommunity.softwareag.com/t/select-needed-to-retrieve-superdescriptor-and-its-column-components-from-connx/177680/1 "2014-07-24T16:27:50Z")
Our developers need the ability to identify the columns that are components of a superdescriptor. They want to be able to do a select against the connx schema to list all the superdescriptors of a table together with all of the components columns that made up each of the super. I see table\_indexes and syscnxStatistics but none would allow me to provide the information that the developers need. ---
**Author:** ![Larry\_McGhaw](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/larry_mcghaw/32/562_2.png) [@Larry\_McGhaw](https://techcommunity.softwareag.com/u/Larry_McGhaw)\ **Post date:** [July 24, 2014, 4:56pm UTC](https://techcommunity.softwareag.com/t/select-needed-to-retrieve-superdescriptor-and-its-column-components-from-connx/177680/2 "2014-07-24T16:56:46Z")
This information is available in the data dictionary viewer utility (not to be confused with the data dictionary administrator). Please have the developer use this to view the superdescriptors and all the columns that make up each superdescriptor. ---
**Author:** ![Min\_Chai](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@Min\_Chai](https://techcommunity.softwareag.com/u/Min_Chai)\ **Post date:** [July 25, 2014, 2:46pm UTC](https://techcommunity.softwareag.com/t/select-needed-to-retrieve-superdescriptor-and-its-column-components-from-connx/177680/3 "2014-07-25T14:46:47Z")
Larry, Is it possible to address this via SQL? We have 100+ developers and we rather not have them each of them install the CDD viewer tool if possible. Thanks, Min ---
**Author:** ![ChristianHeld](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/christianheld/32/138_2.png) [@ChristianHeld](https://techcommunity.softwareag.com/u/ChristianHeld)\ **Post date:** [August 6, 2014, 10:42am UTC](https://techcommunity.softwareag.com/t/select-needed-to-retrieve-superdescriptor-and-its-column-components-from-connx/177680/4 "2014-08-06T10:42:57Z")
Hi! Maybe this is a first step to solve your request: The information\_schema holds two tables: - INFORMATION\_SCHEMA.TABLE\_INDEXES - INFORMATION\_SCHEMA.KEY\_COLUMN\_USAGE I tried to get the information for this SPD: DEFINITION-VIW:FD-BELEG-(0,125) ------------- COMMAND===\> T L DB Name 1 AO D-BETRAG - ``` -------- SOURCE FIELD(S) ------- ``` - ``` KUNDENNUMMER(1-4) ``` - ``` TEILNEHMER(1-4) ``` - ``` BETRAG(1-6) ``` - ``` BUCHUNGSJAHR(1-4) ``` - ``` BELEGKREIS(1-4) ``` - ``` BELEGNUMMER-EXT(1-4) ``` - ``` FAKTURASTELLENNUMMER(1-3) ``` using this sql SELECT ``` TIDX.* ``` , KCUS.CONSTRAINT\_CATALOG , KCUS.CONSTRAINT\_SCHEMA , KCUS.CONSTRAINT\_NAME , KCUS.TABLE\_CATALOG , KCUS.TABLE\_SCHEMA , KCUS.TABLE\_NAME , KCUS.ORDINAL\_POSITION , KCUS.COLUMN\_NAME ## FROM INFORMATION\_SCHEMA.TABLE\_INDEXES TIDX ,INFORMATION\_SCHEMA.KEY\_COLUMN\_USAGE KCUS WHERE TIDX.TABLE\_CATALOG = ‘ADA201’ AND TIDX.TABLE\_SCHEMA = ‘FD’ AND TIDX.TABLE\_NAME = ‘BELEG’ AND TIDX.SHORT\_NAME = ‘AO’ \* AND KCUS.CONSTRAINT\_CATALOG = TIDX.TABLE\_CATALOG AND KCUS.CONSTRAINT\_SCHEMA = TIDX.TABLE\_SCHEMA AND KCUS.TABLE\_NAME = TIDX.TABLE\_NAME ORDER BY ORDINAL\_POSITION I was able to get all involved columns, but I get more records than expected. Maybe the problem is that we do not import SPDs as named columns in the CDD. I guess this is the reason that the column KCUS.CONSTRAINT\_NAME holds no values in our environment. Larry may give an advice whether the retrieval of the information\_schema is an alternative approach. cu Christian ---
**Author:** ![Larry\_McGhaw](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/larry_mcghaw/32/562_2.png) [@Larry\_McGhaw](https://techcommunity.softwareag.com/u/Larry_McGhaw)\ **Post date:** [August 6, 2014, 5:03pm UTC](https://techcommunity.softwareag.com/t/select-needed-to-retrieve-superdescriptor-and-its-column-components-from-connx/177680/5 "2014-08-06T17:03:29Z")
This query returns all of the key columns SELECT \* from INFORMATION\_SCHEMA.KEY\_COLUMN\_USAGE It does not display the super descriptor name however, the only way to see that is in the data dictionary viewer. ---
**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\ **Post date:** [April 2, 2021, 3:31pm UTC](https://techcommunity.softwareag.com/t/select-needed-to-retrieve-superdescriptor-and-its-column-components-from-connx/177680/6 "2021-04-02T15:31:39Z")