sql server - NOCHECK does not disable foreign key references -

sql server - NOCHECK does not disable foreign key references -

table creation script:

create table [dbo].[details]( [id] [int] null, [details] [varchar](max) null ) create table [dbo].[name]( [id] [int] identity(1,1) not replication not null, [name] [varchar](max) null, constraint [pk__name__3213e83f0d384ee4] primary key clustered ( [id] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] ) on [primary] textimage_on [primary] alter table [dbo].[details] nocheck add together constraint [id] foreign key([id]) references [dbo].[name] ([id]) go alter table [dbo].[details] nocheck constraint [id] go

table population script

insert name(name) values (newid()) go 100 insert details (id,details) values(rand()*10,newid()) go 1000

table truncation script

exec sp_msforeachtable "alter table ? nocheck constraint all" exec sp_msforeachtable "truncate table ?" exec sp_msforeachtable "alter table ? check check constraint all"

taken can foreign key constraints temporarily disabled using t-sql?

also tried

alter table name nocheck constraint truncate table name

both cases don't allow me truncate table since foreign key reference there

error message:

cannot truncate table 'dbo.name' because beingness referenced foreign key constraint.

is there way disable fk constraints, delete info in database , reinitialize identities, reenable fk constraints?

i used next 2 scripts disabling foreign keys. have not created them, found them on interwebs, worked me lastly few years. unfortunately can't find source of them longer.

references_sp.sql stored procedure need created before other script runs.

references_run.sql script generates script disabling/enabling foreign keys.

note it's safe run script provided here - not modify database (apart creating , deleting temporary tables) generates script drops , re-create foreign keys.

and scripts.

references_sp.sql:

use [master] go set ansi_nulls on go set quoted_identifier on go /********************************************************************************************* copyright sqlservernation.com * author: tim chapman ***********************************************************************************************/ create procedure [dbo].[sp_showforeignkeyobjects] select object_name(constid) constraintname, object_schema_name(fkeyid) + '.' + object_name(fkeyid) + '.' + col_name(fkeyid, fkey) foreignkeyobject, object_schema_name(rkeyid) + '.' + object_name(rkeyid) + '.' + col_name(rkeyid, rkey) referencekeyobject, col_name(fkeyid, fkey) foreignkeycolumn, col_name(rkeyid, rkey) referencekeycolumn, constid constraintid, object_schema_name(fkeyid) + '.' + object_name(fkeyid) foreignkeytable, fkeyid foreignkeyid, object_schema_name(rkeyid) + '.' + object_name(rkeyid) referencekeytable, rkeyid referencekeyid, keyno keysequencenumber sysforeignkeys order object_name(rkeyid) asc, col_name(rkeyid, rkey) go

references_run.sql:

if object_id('tempdb..#fk')>0 drop table #fk if object_id('tempdb..#const')>0 drop table #const create table #fk ( constraintname varchar(255), foreignkeyobject varchar(255), referenceobject varchar(255), foreignkeycolumn varchar(255), referencekeycolumn varchar(255), constraintid int, foreignkeytable varchar(255), foreignkeyid int, referencekeytable varchar(255), referencekeyid int, keysequencenumber smallint ) create table #const ( constraintid int, fbuildfield varchar(2000) default(''), rbuildfield varchar(2000) default(''), countfield smallint ) insert #fk exec sp_showforeignkeyobjects set nocount on declare tempcursor cursor read_only select f.constraintname , f.foreignkeyobject , f.referenceobject , f.foreignkeycolumn , f.referencekeycolumn, f.constraintid , f.foreignkeyid , f.referencekeyid , f.keysequencenumber, f.foreignkeytable, f.referencekeytable #fk f inner bring together ( select constraintid, max(keysequencenumber) maxseq #fk k grouping k.constraintid )b on f.constraintid = b.constraintid , f.keysequencenumber = b.maxseq declare @constraintname varchar(255), @foreignkeyobject varchar(255), @referenceobject varchar(255), @foreignkeycolumn varchar(255), @referencekeycolumn varchar(255), @constraintid int, @foreignkeyid int, @referencekeyid int, @keysequencenumber smallint, @foreignkeytable varchar(255), @referencekeytable varchar(255) open tempcursor fetch next tempcursor @constraintname , @foreignkeyobject , @referenceobject , @foreignkeycolumn , @referencekeycolumn, @constraintid , @foreignkeyid , @referencekeyid, @keysequencenumber, @foreignkeytable , @referencekeytable while (@@fetch_status <> -1) begin declare tempcursor2 cursor read_only select constraintid, foreignkeycolumn, referencekeycolumn, keysequencenumber #fk constraintid = @constraintid order constraintid, keysequencenumber asc declare @constraintid2 int, @foreignkeycolumn2 varchar(255), @referencekeycolumn2 varchar(255), @keysequencenumber2 smallint declare @fkeybuildfield varchar(1000), @rkeybuildfield varchar(1000), @cnt smallint open tempcursor2 select @fkeybuildfield = '', @rkeybuildfield = '', @cnt = 0 fetch next tempcursor2 @constraintid2 , @foreignkeycolumn2 , @referencekeycolumn2 , @keysequencenumber2 while (@@fetch_status <> -1) begin set @cnt = @cnt + 1 select @fkeybuildfield = @fkeybuildfield + isnull(@foreignkeycolumn2,'')+ case when @foreignkeycolumn2 null '' else case when @keysequencenumber = @keysequencenumber2 '' else ',' end end select @rkeybuildfield = @rkeybuildfield + isnull(@referencekeycolumn2,'')+ case when @referencekeycolumn2 null '' else case when @keysequencenumber = @keysequencenumber2 '' else ',' end end insert #const ( constraintid , fbuildfield , rbuildfield, countfield ) values ( @constraintid, @fkeybuildfield, @rkeybuildfield, @cnt ) fetch next tempcursor2 @constraintid2 , @foreignkeycolumn2 , @referencekeycolumn2 , @keysequencenumber2 end close tempcursor2 deallocate tempcursor2 fetch next tempcursor @constraintname , @foreignkeyobject , @referenceobject , @foreignkeycolumn , @referencekeycolumn, @constraintid , @foreignkeyid , @referencekeyid , @keysequencenumber , @foreignkeytable , @referencekeytable end close tempcursor deallocate tempcursor select 'alter table ' + fktable + ' drop constraint ' + object_name(a.constraintid) dropkeys, 'alter table ' + fktable + ' nocheck add together constraint ' + object_name(a.constraintid) + ' foreign key(' + fbuildfield + ') references ' + rktable + '(' + rbuildfield+')' buildkeys ,* #const bring together ( select constraintid, max(countfield) maxcount #const grouping constraintid ) b on a.constraintid = b.constraintid , a.countfield = b.maxcount bring together ( select distinct constraintid, '[' + object_schema_name(foreignkeyid) + '].[' + object_name(foreignkeyid) + ']' fktable, '[' + object_schema_name(referencekeyid) + '].[' + object_name(referencekeyid) + ']' rktable #fk ) c on a.constraintid = c.constraintid drop table #const

sql-server tsql sql-server-2012