做了一个实验,你可以参考下面的代码,来删除外键约束。注意下面的代码,不要在正式环境中使用。先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 下面的语句要在2005上运行,可;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.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]; */ 以生成删除外键的语句,需要复制出来,然后放到再执行:
做了一个实验,你可以参考下面的代码,来删除外键约束。注意下面的代码,不要在正式环境中使用。先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 下面的语句要在2005上运行,可以生成删除外键的语句,需要复制出来,然后放到再执行:;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.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.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 +']('+TBR.column_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_c] add constraint [FK__tb_c__idd__0A9D95DB] foreign key(idd) references [dbo].[tb_b](idd) alter table [dbo].[tb_b] add constraint [FK__tb_b__id__05D8E0BE] foreign key(id) references [dbo].[tb](id) */
谢谢13楼辛苦帮忙,我已经导出sql2000数据库的脚本,数据库里脚本有删除语句。
你看这个例子:create table tb(id int primary key ,vv varchar(10))insert into tb values(1,'aa') go
create table tb_b( idd int primary key, id int --foreign key references tb(id) )insert into tb_b values(1,2) --id不在主表中 go--新增外键约束,不会报错,with nocheck对于之前已经存在的数据,不会进行检测 ALTER TABLE [dbo].[tb_b] WITH noCHECK ADD FOREIGN KEY([id]) REFERENCES [dbo].[tb] ([id]) GO --会报错 ,在建立上面的约束后,再次插入,就会报错了 insert into tb_b values(2,2) --id不在主表中
但是就像上面版主说的,如果你用到了2005的新功能,最典型的,你的存储过程、函数、应用程序中用到了row_number等函数,那么你就麻烦了,你得修改所有这些,和2000不兼容的地方,这个工作量很大,而且很容易遗漏。2、就是数据导入,这个时候可以考虑把所有表的约束,特别是外键约束,禁用,然后再导入数据,最后在恢复这些约束。
做了一个实验,你可以参考下面的代码,来删除外键约束。注意下面的代码,不要在正式环境中使用。先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
下面的语句要在2005上运行,可;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];
*/
以生成删除外键的语句,需要复制出来,然后放到再执行:
/*
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
下面的语句要在2005上运行,可以生成删除外键的语句,需要复制出来,然后放到再执行:;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 +']('+TBR.column_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_c] add constraint [FK__tb_c__idd__0A9D95DB] foreign key(idd) references [dbo].[tb_b](idd)
alter table [dbo].[tb_b] add constraint [FK__tb_b__id__05D8E0BE] foreign key(id) references [dbo].[tb](id)
*/
values(1,'aa')
go
create table tb_b(
idd int primary key,
id int --foreign key references tb(id)
)insert into tb_b
values(1,2) --id不在主表中
go--新增外键约束,不会报错,with nocheck对于之前已经存在的数据,不会进行检测
ALTER TABLE [dbo].[tb_b] WITH noCHECK ADD FOREIGN KEY([id])
REFERENCES [dbo].[tb] ([id])
GO
--会报错 ,在建立上面的约束后,再次插入,就会报错了
insert into tb_b
values(2,2) --id不在主表中
为什么alter table fact_goods enable trigger all不起作用?
另外一个表重新建立,重新建立主键,外键,索引,所有的关系是否要重新编译,触发器,存储过程,视图会失效吗?
表的问题,重建主键的话,会产生重编译,存储过程没问题,触发器我不敢说,但是视图,最好执行sp_refreshview
http://blog.csdn.net/dba_huangzj/article/details/8426684
有SQL SERVER2000查询统计过时的语句吗
sql server有内部机制去自动更新过时的统计信息,但是不够准确,而且也没有语句,能查询统计信息是否过时。所以一般就是直接更新统计信息,就可以了