如何识别sql中包含默认约束和检查约束的表

Question

CREATE TABLE Persons (
ID int  NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int  CHECK (Age>=18),
City varchar(255) DEFAULT 'Sandnes'
);

预期输出为

----------------------------------------------------
fields               |     rule
-------------------------------------------
eid                     UNIQUE
Firstname               not null
Lastname                not null
age                     >18
city                   def=sandnes
---------------------------------------

我怎样才能做到这一点？

Answer 1

类似的东西

exec sp_Columns Persons 

和

SELECT OBJECT_NAME(object_id) AS ConstraintName, SCHEMA_NAME(schema_id) AS SchemaName, type_desc AS ConstraintType
FROM sys.objects
WHERE (type_desc LIKE '%CONSTRAINT') AND (OBJECT_NAME(parent_object_id) = 'Persons')

将适合您对MS SQL的需求。

Answer 2

类似的东西可以在Postgresql 上工作

select table1.table_name, table2.check_clause
from information_schema.table_constraints table1
join 
information_schema.check_constraints table2 
on table1.constraint_name=table2.constraint_name 
and 
table1.table_schema='<your schema name>';

table_name | table_schema |     check_clause      
------------+--------------+-----------------------
persons    | public       | firstname IS NOT NULL
persons    | public       | id IS NOT NULL
persons    | public       | lastname IS NOT NULL
persons    | public       | ((age >= 18))

这是上述查询的预期结果。。

Answer 3

以下是解决方案及其运行情况。如果你喜欢这个解决方案，请投票。

CREATE TABLE Persons (
ID int  constraint UniqueType unique,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int constraint CheckType CHECK (Age>=18),
City varchar(255) constraint DefaultType DEFAULT 'Sandnes')