SQLServer

-- 查询表的主键约束信息
SELECT kc.name AS constraint_name,
       col.name AS column_name
FROM sys.key_constraints kc
JOIN sys.index_columns ic ON kc.parent_object_id = ic.object_id AND kc.unique_index_id = ic.index_id
JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
WHERE kc.parent_object_id = OBJECT_ID('your_table_name') AND kc.type = 'PK';

-- 查询表的唯一键约束信息
SELECT kc.name AS constraint_name,
       col.name AS column_name
FROM sys.key_constraints kc
JOIN sys.index_columns ic ON kc.parent_object_id = ic.object_id AND kc.unique_index_id = ic.index_id
JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
WHERE kc.parent_object_id = OBJECT_ID('your_table_name') AND kc.type = 'UQ';

-- 查询表的检查约束信息
SELECT cc.name AS constraint_name,
       OBJECT_NAME(parent_object_id) AS table_name,
       cc.definition AS check_definition
FROM sys.check_constraints cc
WHERE cc.parent_object_id = OBJECT_ID('your_table_name');

-- 查询表的默认约束信息
SELECT dc.name AS constraint_name,
       OBJECT_NAME(parent_object_id) AS table_name,
       col.name AS column_name,
       dc.definition AS default_definition
FROM sys.default_constraints dc
JOIN sys.columns col ON dc.parent_object_id = col.object_id AND dc.parent_column_id = col.column_id
WHERE dc.parent_object_id = OBJECT_ID('your_table_name');

-- 查询表的外键约束信息
SELECT fk.name AS constraint_name,
       OBJECT_NAME(fk.parent_object_id) AS parent_table,
       col.name AS parent_column,
       OBJECT_NAME(fk.referenced_object_id) AS referenced_table,
       rcol.name AS referenced_column
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns col ON fkc.parent_object_id = col.object_id AND fkc.parent_column_id = col.column_id
JOIN sys.columns rcol ON fkc.referenced_object_id = rcol.object_id AND fkc.referenced_column_id = rcol.column_id
WHERE fk.parent_object_id = OBJECT_ID('your_table_name');

create table edi_test(
  a varchar(100) not null,
  b varchar(100) default('-'),
  c varchar(100)
);

SELECT dc.name AS constraint_name,
       OBJECT_NAME(parent_object_id) AS table_name,
       col.name AS column_name,
       dc.definition AS default_definition
FROM sys.default_constraints dc
JOIN sys.columns col ON dc.parent_object_id = col.object_id AND dc.parent_column_id = col.column_id
WHERE dc.parent_object_id = OBJECT_ID('edi_test');

alter table dbo.edi_test drop column b; --SQL Error [5074] [S1000]: The object 'DF__edi_test__b__2E1BDC42' is dependent on column 'b'.
alter table dbo.edi_test drop constraint DF__edi_test__b__2E1BDC42;
alter table dbo.edi_test drop column b; 

alter table dbo.edi_test drop column a;

create table edi_test(
  a varchar2(100) not null,
  b varchar2(100) default('-'),
  c varchar2(100)
);

select * from user_constraints where table_name = 'EDI_TEST';

alter table edi_test drop column b; 

alter table edi_test drop column a; 

CREATE TYPE dbo.String FROM VARCHAR(255);

DECLARE @StringValue dbo.String = 'some value less than 256 characters';
SELECT  @StringValue;

SELECT * FROM sys.types;

SELECT  SelectedType.[name] AS SelectedTypeName
        , BaseType.[name] AS BaseTypeName
        , SelectedType.max_length AS SelectedTypeMaxBytes
        , SelectedType.[precision] AS SelectedTypePrecision
        , SelectedType.scale AS SelectedTypeScale
        , SelectedType.default_object_id AS SelectedTypeId
        , BaseType.default_object_id AS BaseTypeId
  FROM  sys.types SelectedType 
  JOIN  sys.types BaseType
    ON  BaseType.system_type_id = SelectedType.system_type_id
 WHERE  BaseType.is_user_defined = 0 
   AND  SelectedType.is_user_defined = 1
   AND  BaseType.[name] <> 'sysname'
 ORDER BY SelectedType.[name];

create table #edi_temp (
	head_id varchar(25),
	transfer_time varchar(100)
);

select * from #edi_temp;

insert into #edi_temp values
('H00039827', '2019/3/27 14:15:13'),
('H00039871', '2019/5/13 13:10:49'),
('H00039872', '2019/5/13 13:10:49'),
('H00039873', '2019/5/13 13:10:49')

update edi_head
   set transfer_time = cast(b.transfer_time as datetime) from edi_head a, #edi_temp b
 where a.head_id = b.head_id;

--isnull 类似于 Oracle nvl
select isnull(null, 'S'); --S
select isnull('R', 'S');  --R

--getdate() 类似于 Oracle sysdate
--如下类似于 Oracle: select sysdate from dual;
select getdate();