SQL - colunas de uma tabela e seus atributos

Menu principal [Fechar]

Introdução

Divisões

Identification Division

Environment Division

Data Division

Procedure Division

Comandos CICS

VSAM - File Status

Quadros importantes

SQL - Structured Query Language

Outros programas

Rotinas prontas

Palavras reservadas

Introdução ao Oracle

Sites de busca

Sites interessantes

Meu e-mail

O SELECT abaixo mostra as colunas de uma tabela e seus atributos

SELECT A.TBNAME TABELA , A.COLNO SEQ , A.NAME COLUNA , A.COLTYPE TIPO , '{' || SUBSTR(DIGITS(A.LENGTH), 2, 4) || ',' || REPLACE(SUBSTR(DIGITS(A.SCALE), 5, 1),'0', ' ') || '}' TAMANHO , SUBSTR(DIGITS(VALUE(C.COLSEQ, 0)), 9, 2) KEY FROM SYSIBM.SYSCOLUMNS A , SYSIBM.SYSINDEXES B , SYSIBM.SYSKEYS C WHERE A.TBNAME LIKE '%CADTB002_DEB_FOLHA%' AND A.TBCREATOR = 'CAD' AND A.TBNAME = B.TBNAME AND A.TBCREATOR = B.TBCREATOR AND B.NAME = C.IXNAME AND B.CREATOR = C.IXCREATOR AND A.NAME = C.COLNAME UNION SELECT A.TBNAME TABELA , A.COLNO SEQ , A.NAME COLUNA , A.COLTYPE TIPO , '{' || SUBSTR(DIGITS(A.LENGTH), 2, 4) || ',' || REPLACE(SUBSTR(DIGITS(A.SCALE), 5, 1),'0', ' ') || '}' TAMANHO , ' ' KEY FROM SYSIBM.SYSCOLUMNS A WHERE A.TBNAME LIKE '%CADTB002_DEB_FOLHA%' AND A.TBCREATOR = 'CAD' AND A.TBNAME || A.NAME NOT IN ( SELECT A.TBNAME || A.NAME FROM SYSIBM.SYSCOLUMNS A , SYSIBM.SYSINDEXES B , SYSIBM.SYSKEYS C WHERE A.TBNAME LIKE '%CADTB002_DEB_FOLHA%' AND A.TBCREATOR = 'CAD' AND A.TBNAME = B.TBNAME AND A.TBCREATOR = B.TBCREATOR AND B.NAME = C.IXNAME AND B.CREATOR = C.IXCREATOR AND A.NAME = C.COLNAME ) ORDER BY 1, 2

Resultado da pesquisa acima:

REPORT LINE 1 POS 1 79 TABELA SEQ COLUNA TIPO TAMANHO KEY ------------------ ------ ------------------ -------- -------- --- CADTB002_DEB_FOLHA 1 NU_CONTRATO DECIMAL {0012, } 01 CADTB002_DEB_FOLHA 2 NU_VENCIMENTO INTEGER {0004, } 02 CADTB002_DEB_FOLHA 3 NU_MATRICULA INTEGER {0004, } CADTB002_DEB_FOLHA 4 NU_AGENCIA SMALLINT {0002, } CADTB002_DEB_FOLHA 5 NU_PRESTACAO SMALLINT {0002, } CADTB002_DEB_FOLHA 6 VR_PRESTACAO DECIMAL {0011,2} CADTB002_DEB_FOLHA 7 IC_CREDITO SMALLINT {0002, } CADTB002_DEB_FOLHA 8 DT_CARGA DATE {0004, } CADTB002_DEB_FOLHA 9 IC_GRAVA_REGISTRO SMALLINT {0002, } *** END *** 1=Help 2= 3=End 4=Print 5=Chart 6=Query 7=Backward 8=Forward 9=Form 10=Left 11=Right 12= OK, this is the REPORT from your RUN command. COMMAND ===> SCROLL ===> PAGE

O SELECT abaixo mostra as colunas de uma tabela e seus atributos

SELECT A.TBNAME TABELA , A.COLNO SEQ , A.NAME COLUNA , A.COLTYPE TIPO , '{' || SUBSTR(DIGITS(A.LENGTH), 2, 4) || ',' || REPLACE(SUBSTR(DIGITS(A.SCALE), 5, 1),'0', ' ') || '}' TAMANHO , SUBSTR(DIGITS(VALUE(C.COLSEQ, 0)), 9, 2) KEY FROM SYSIBM.SYSCOLUMNS A , SYSIBM.SYSINDEXES B , SYSIBM.SYSKEYS C WHERE A.TBNAME LIKE '%CADTB007_CNSLDCO%' AND A.TBCREATOR = 'CAD' AND A.TBNAME = B.TBNAME AND A.TBCREATOR = B.TBCREATOR AND B.NAME = C.IXNAME AND B.CREATOR = C.IXCREATOR AND A.NAME = C.COLNAME UNION SELECT A.TBNAME TABELA , A.COLNO SEQ , A.NAME COLUNA , A.COLTYPE TIPO , '{' || SUBSTR(DIGITS(A.LENGTH), 2, 4) || ',' || REPLACE(SUBSTR(DIGITS(A.SCALE), 5, 1),'0', ' ') || '}' TAMANHO , ' ' KEY FROM SYSIBM.SYSCOLUMNS A WHERE A.TBNAME LIKE '%CADTB007_CNSLDCO%' AND A.TBCREATOR = 'CAD' AND A.TBNAME || A.NAME NOT IN ( SELECT A.TBNAME || A.NAME FROM SYSIBM.SYSCOLUMNS A , SYSIBM.SYSINDEXES B , SYSIBM.SYSKEYS C WHERE A.TBNAME LIKE '%CADTB007_CNSLDCO%' AND A.TBCREATOR = 'CAD' AND A.TBNAME = B.TBNAME AND A.TBCREATOR = B.TBCREATOR AND B.NAME = C.IXNAME AND B.CREATOR = C.IXCREATOR AND A.NAME = C.COLNAME ) ORDER BY 1, 2

Resultado da pesquisa acima:

REPORT LINE 1 POS 1 79 TABELA SEQ COLUNA TIPO TAMANHO KEY ------------------ ------ ------------------ -------- -------- --- CADTB007_CNSLDCO 1 DT_INICIAL DATE {0004, } CADTB007_CNSLDCO 2 DT_FINAL DATE {0004, } 01 CADTB007_CNSLDCO 3 NU_UNO SMALLINT {0002, } 02 CADTB007_CNSLDCO 4 NU_DV_UNO SMALLINT {0002, } CADTB007_CNSLDCO 5 NU_GRUPO SMALLINT {0002, } 03 CADTB007_CNSLDCO 6 QT_CONTRATACAO SMALLINT {0002, } CADTB007_CNSLDCO 7 VR_CONTRATACAO DECIMAL {0016,2} CADTB007_CNSLDCO 8 QT_CNTO_ESTQE_ATL SMALLINT {0002, } CADTB007_CNSLDCO 9 VR_CNTO_ESTQE_ATL DECIMAL {0016,2} CADTB007_CNSLDCO 10 QT_CNTO_ESTQE_ANTR SMALLINT {0002, } CADTB007_CNSLDCO 11 VR_CNTO_ESTQE_ANTR DECIMAL {0016,2} CADTB007_CNSLDCO 12 QT_CNTO_ESTQE_INL SMALLINT {0002, } CADTB007_CNSLDCO 13 VR_CNTO_ESTQE_INL DECIMAL {0016,2} CADTB007_CNSLDCO 14 QT_CNTO_CANCELADO SMALLINT {0002, } CADTB007_CNSLDCO 15 VR_CNTO_CANCELADO DECIMAL {0016,2} *** END *** 1=Help 2= 3=End 4=Print 5=Chart 6=Query 7=Backward 8=Forward 9=Form 10=Left 11=Right 12= OK, FORWARD performed. Please proceed. COMMAND ===> SCROLL ===> PAGE