Table columns

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Fri, 25 Nov 2005 10:13 am

Salve galera ...

Following, I'm doing a query to search the columns in a given table, until ai também, I have a query that seeks the pk columns from the same table, but now I want to do all this in a query only ... till I got, but it returns only the columns pk, tried using the outer joined but I couldn't ... does anyone have a tip ai ... follows the code.
Code: Select all
SELECT c.column_name,
       c.data_type,
       c.data_length,
       c.data_precision,
       c.data_scale,
       c.nullable,
       decode(u.column_name,NULL,NULL,'PK') PK
  FROM user_tab_columns  c,
       user_constraints  q,
       user_cons_columns u
WHERE c.table_name      = upper('QS_NOTA_FISCAL')
   AND c.table_name      = q.table_name
   AND c.column_name     = u.column_name
   AND q.constraint_type = 'P'
   AND q.table_name      = u.table_name
   AND q.constraint_name = u.constraint_name
ORDER BY c.column_id
thanks
anderson
Location: Toledo - PR

Anderson Nuernberg
---

Poston Fri, 25 Nov 2005 10:28 am

Does a UNION!!
Something like this:
Code: Select all
select bla bla bla
from tabela1
  UNION ALL
select bla bla bla
from tabela2
does it not solve?
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Fri, 25 Nov 2005 12:39 pm

dr_gori, for what I priciso, the union will not work ... I'vê done these tests.
anderson
Location: Toledo - PR

Anderson Nuernberg
---

Poston Fri, 25 Nov 2005 12:54 pm

Shows aí as you want it to be the return of your SQL.
Then we can give a force!!! :-o
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Fri, 25 Nov 2005 1:09 pm

There you go ... the following code shows the columns of the table.
Code: Select all
SELECT column_name, data_type, data_length, data_precision, data_scale, nullable
  FROM user_tab_columns
WHERE table_name = upper(prm_nome_tabela)
ORDER BY column_id
the following code shows the pk of the table ...
Code: Select all
select u.table_name
      ,u.constraint_name
      ,u.column_name
  from user_constraints q
      ,user_cons_columns u
where q.table_name        like upper(prm_nome_tabela)
   and q.constraint_type   = 'P'
   and u.table_name        = q.table_name
   and u.constraint_name   = q.constraint_name
then order a single select, but only shows the pk ...
Code: Select all
SELECT c.column_name,
       c.data_type,
       c.data_length,
       c.data_precision,
       c.data_scale,
       c.nullable,
       decode(u.column_name,NULL,NULL,'PK') PK
  FROM user_tab_columns  c,
       user_constraints  q,
       user_cons_columns u
WHERE c.table_name      = upper(prm_nome_tabela)
   AND c.table_name      = q.table_name
   AND c.column_name     = u.column_name
   AND q.constraint_type = 'P'
   AND q.table_name      = u.table_name
   AND q.constraint_name = u.constraint_name
ORDER BY c.column_id
Code: Select all
COLUMN_NAME               DATA_TYPE   DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE PK
------------------------- ----------- ----------- -------------- ---------- -------- --
CD_TIPO_PESSOA_EMPRESA    NUMBER               22              3          0 N        PK
CD_PESSOA_EMPRESA         NUMBER               22              7          0 N        PK
CD_TIPO_PESSOA_FILIAL     NUMBER               22              3          0 N        PK
CD_PESSOA_FILIAL          NUMBER               22              7          0 N        PK
CD_NOTA_FISCAL            NUMBER               22              7          0 N        PK
and should be something like this ... in the last column showing whether the field is pk or not ...
Code: Select all
COLUMN_NAME               DATA_TYPE   DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE PK
------------------------- ----------- ----------- -------------- ---------- -------- --
CD_TIPO_PESSOA_EMPRESA    NUMBER               22              3          0 N        PK
CD_PESSOA_EMPRESA         NUMBER               22              7          0 N        PK
CD_TIPO_PESSOA_FILIAL     NUMBER               22              3          0 N        PK
CD_PESSOA_FILIAL          NUMBER               22              7          0 N        PK
CD_NOTA_FISCAL            NUMBER               22              7          0 N        PK
CD_SERIE_NOTA_FISCAL      VARCHAR2              3                           N
NR_NOTA_FISCAL            NUMBER               22              7          0 N
NR_PIM_NOTA_FISCAL        NUMBER               22              6          0 N
ID_TIPO_NOTA_FISCAL       VARCHAR2              2                           N
CD_OPERACAO_FISCAL_MAT    NUMBER               22              4          0 Y
CD_OPERACAO_FISCAL_SRV    NUMBER               22              4          0 Y
CD_TIPO_DOCUMENTO         NUMBER               22              3          0 N
CD_FORMA_PAGAMENTO        NUMBER               22              3          0 Y
DT_EMISSAO                DATE                  7                           N
DT_SAIDA                  DATE                  7                           Y
HR_SAIDA                  DATE                  7                           Y
...
...
...
...
but as I said in that last select, just returned the pk ´ s ...
anderson
Location: Toledo - PR

Anderson Nuernberg
---

Poston Fri, 25 Nov 2005 2:12 pm

I did a little differently:
Code: Select all
SELECT
  X.COLUMN_NAME
, X.data_type
, X.data_length
, X.data_precision
, X.data_scale
, X.nullable
, Z.PK
FROM
  USER_TAB_COLUMNS X
, (select u.column_name, 'PK' PK
   from user_constraints q
       ,user_cons_columns u
   where q.table_name        like upper('CAM_COTACOES')
     and q.constraint_type   = 'P'
     and u.table_name        = q.table_name
     and u.constraint_name   = q.constraint_name
  ) Z
WHERE X.column_name = Z.COLUMN_NAME(+)
  AND X.TABLE_NAME like upper('CAM_COTACOES')
ORDER BY X.column_id
:-
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Fri, 25 Nov 2005 2:29 pm

meeeeeeeeeeeeeuuuuuuuuuuu ... that's right ... dr_gori was cool, that's exactly what I wanted ... thanks.

[] ´ s:-
anderson
Location: Toledo - PR

Anderson Nuernberg
---



Return to SQL

Who is online

Users browsing this forum: No registered users and 2 guests