oracle中alltable表头中文,在Oracle SQL中顯示表的所有約束的名稱

I have defined a name for each of the constraint for the multiple tables that I have created in Oracle SQL.

我為在Oracle SQL中創建的多個表定義了每個約束的名稱。

The problem is that to drop a constraint for the column of a particular table I need to know the name that I have supplied for each constraints, which I have forgotten.

問題是，要刪除特定表的列的約束，我需要知道為每個約束提供的名稱，我已經忘記了這個名稱。

How do I list out all the names of constraints that I have specified for each column of a table?

如何列出我為表的每一列指定的所有約束的名稱?

Is there any SQL statement for doing so?

這樣做有SQL語句嗎?

4 个解决方案

#1

111

You need to query the data dictionary, specifically the USER_CONS_COLUMNS view to see the table columns and corresponding constraints:

您需要查詢數據字典，特別是USER_CONS_COLUMNS視圖，以查看表列和相應的約束:

SELECT *

FROM user_cons_columns

WHERE table_name = '';

FYI, unless you specifically created your table with a lower case name (using double quotes) then the table name will be defaulted to upper case so ensure it is so in your query.

順便提一下，除非您專門用小寫名稱(使用雙引號)創建了表，否則表名將默認為大寫，因此請確保在查詢中是如此。

If you then wish to see more information about the constraint itself query the USER_CONSTRAINTS view:

如果您希望看到關於約束本身的更多信息，請查詢USER_CONSTRAINTS視圖:

SELECT *

FROM user_constraints

WHERE table_name = ''

AND constraint_name = '';

If the table is held in a schema that is not your default schema then you might need to replace the views with:

如果該表的模式不是您的默認模式，那么您可能需要將視圖替換為:

all_cons_columns

and

和

all_constraints

adding to the where clause:

加入where條款:

AND owner = ''

#2

11

SELECT * FROM USER_CONSTRAINTS

#3

8

maybe this can help:

也許這可以幫助:

SELECT constraint_name, constraint_type, column_name

from user_constraints natural join user_cons_columns

where table_name = "my_table_name";

cheers

干杯

#4

7

select constraint_name,constraint_type

from user_constraints

where table_name = 'YOUR TABLE NAME';

note: table name should be in caps.

注意:表名應該用大寫。

In case you don't know the name of the table then,

如果你不知道表格的名字，

select constraint_name,constraint_type,table_name

from user_constraints;