SQLServer的日常维护语句(一)

1、删除外键约束，建立外键约束

先建立3个表：

/*
drop table tb
drop table tb_b
drop table tb_c
*/

--建立3个关联的表
create table tb(id int primary key ,vv varchar(10))


create table tb_b(
idd int primary key,
id int foreign key references tb(id)
)

create table tb_c(
iddd int primary key,
idd int foreign key references tb_b(idd)
)
go 

可以生成删除外键的语句，需要复制出来，然后放到再执行：

;WITH FK  --外键约束
AS
(
	SELECT
		SCH.name as foreign_schema_name,   --外键schema名
		FK.name as foreign_name,           --外键名
		FK.is_disabled ,                   --是否禁用
		
		FK.delete_referential_action_desc as delete_action,
		FK.update_referential_action_desc as update_action,
		
		FKC.constraint_column_id,		   --约束列的id
		FKC.parent_object_id,              --父对象的id
		FKC.parent_column_id,              --父对象列的id
		
		
		FKC.referenced_object_id,          --被引用的对象
		FKC.referenced_column_id           --被引用的对象中的列
	 FROM sys.foreign_keys FK
		INNER JOIN sys.foreign_key_columns FKC
			ON FK.object_id = FKC.constraint_object_id
		INNER JOIN sys.schemas SCH
			ON FK.schema_id = SCH.schema_id
),

TB   --表和列
AS
(
	SELECT 
		TB.object_id,
		SCH.name as schema_name,
		TB.name as table_name,
		C.column_id as column_id,
		C.name as column_name		
	FROM sys.tables TB WITH(NOLOCK)
		INNER JOIN sys.columns C WITH(NOLOCK)
			ON TB.object_id = C.object_id
		INNER JOIN sys.schemas SCH WITH(NOLOCK)
			ON TB.schema_id = SCH.schema_id
	WHERE TB.is_ms_shipped = 0  -- 此条件表示仅查询不是由内部 SQL Server 组件创建对象
)

SELECT
    'alter table ['+TBP.schema_name+'].['+TBP.table_name+
    '] drop constraint ['+FK.foreign_name+'];' as '删除外键的语句,复制出来后运行'
FROM FK
INNER JOIN TB TBP
		ON FK.parent_object_id = TBP.object_id
		   AND FK.parent_column_id = TBP.column_id
		   
INNER JOIN TB TBR
		ON FK.referenced_object_id = TBR.object_id
		   AND FK.referenced_column_id = TBR.column_id
/*
删除外键的语句,复制出来后运行
alter table [dbo].[tb_b] drop constraint [FK__tb_b__id__6754599E];
alter table [dbo].[tb_c] drop constraint [FK__tb_c__idd__6C190EBB];
*/

;WITH FK  --外键约束
AS
(
	SELECT
		SCH.name as foreign_schema_name,   --外键schema名
		FK.name as foreign_name,           --外键名
		FK.is_disabled ,                   --是否禁用
		
		FK.delete_referential_action_desc as delete_action,
		FK.update_referential_action_desc as update_action,
		
		FKC.constraint_column_id,		   --约束列的id
		FKC.parent_object_id,              --父对象的id
		FKC.parent_column_id,              --父对象列的id
		
		
		FKC.referenced_object_id,          --被引用的对象
		FKC.referenced_column_id           --被引用的对象中的列
	 FROM sys.foreign_keys FK
		INNER JOIN sys.foreign_key_columns FKC
			ON FK.object_id = FKC.constraint_object_id
		INNER JOIN sys.schemas SCH
			ON FK.schema_id = SCH.schema_id
),

TB   --表和列
AS
(
	SELECT 
		TB.object_id,
		SCH.name as schema_name,
		TB.name as table_name,
		C.column_id as column_id,
		C.name as column_name		
	FROM sys.tables TB WITH(NOLOCK)
		INNER JOIN sys.columns C WITH(NOLOCK)
			ON TB.object_id = C.object_id
		INNER JOIN sys.schemas SCH WITH(NOLOCK)
			ON TB.schema_id = SCH.schema_id
	WHERE TB.is_ms_shipped = 0  -- 此条件表示仅查询不是由内部 SQL Server 组件创建对象
)

SELECT
    'alter table ['+TBP.schema_name+'].['+TBP.table_name+
    '] add constraint ['+FK.foreign_name+'] '+
    ' foreign key('+TBP.column_name+') references [' +
    TBR.schema_name +'].['+ TBR.table_name +']