October 28, 2010 at 7:01 am
i am vipul and i use this method to truncate all table from database. but constraint was not enabling by
Exec sp_MSforeachtable 'alter table ? check constraint all'
print 'all constraint enable'
MESSAGE WILL APPEAR THAT- 'all constraint enable'
BUT WHEN I CHECK THE DATABASE ALL CONSTRAINT DISABLE
SO WHAT I SHOULD DO ENABLE AGAIN ALL THE CONSTRAINT
VIPUL SACHAN
October 28, 2010 at 7:28 am
The script on the link below does a very good job of this without making any schema modifications, so it is a safer way to go.
Truncate All Tables
June 2, 2011 at 4:47 am
Just a thought, but if TRUNCATE then likely will reset SEEDS and there might be some cases where these might need to be kept? Maybe. Possibly. Especially if fed through to another system or report? Maybe.
March 14, 2016 at 4:21 am
Can explain me why you create the table T_FK_Xref?
For rollback?
March 14, 2016 at 4:45 am
use database
GO
/* ----- Drop Global Temp Table ##CompaniesTables if Exist ----- */
IF OBJECT_ID('tempdb..##ConstraintsFkTable') IS NOT NULL DROP Table ##ConstraintsFkTable
/* ----- Get the List of All constraints Tables ---- */
/* ----- And insert the result into global temp table ##ConstraintsFkTable ----- */
SELECT object_name(constid) as ConstraintName,object_name(rkeyid) MasterTable
,sc2.name MasterColumn
,object_name(fkeyid) ChildTable
,sc1.name ChildColumn
,cast (sf.keyno as int) FKOrder
into ##ConstraintsFkTable
FROM sysforeignkeys sf
INNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid
INNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid
ORDER BY rkeyid,fkeyid,keyno
go
declare @ConstraintName varchar (max) -- Name of the Constraint
declare @ChildTable varchar (max) -- Name of Child Table
declare @MasterTable varchar (max)--Name of Parent Table
declare @ChildColumn varchar (max)--Column of Child Table FK
declare @MasterColumn varchar (max)-- Parent Column PK
declare @FKOrder smallint -- Fk order
declare @sqlcmd varchar (max) --Dynamic Sql String
declare drop_constraints cursor
fast_forward
for
SELECT ConstraintName, MasterTable
,MasterColumn
,ChildTable
,ChildColumn
,FKOrder
FROM ##ConstraintsFkTable
ORDER BY MasterTable,ChildTable,FKOrder
open drop_constraints
fetch next from drop_constraints
into
@ConstraintName
,@MasterTable
,@MasterColumn
,@ChildTable
,@ChildColumn
,@FKOrder
while @@Fetch_status = 0
begin
-- Create Dynamic Sql to drop constraint
select @sqlcmd = 'alter table '+@ChildTable+' drop constraint '+@ConstraintName--+' foreign key '+'('+@ChildColumn+')'+' references '+@MasterTable+' ('+@MasterColumn+')'+' on delete no action on update no action'
If EXISTs (select object_name(constid) from sysforeignkeys where object_name(constid) = @ConstraintName)
exec (@sqlcmd)
fetch next from drop_constraints
into
@ConstraintName
,@MasterTable
,@MasterColumn
,@ChildTable
,@ChildColumn
,@FKOrder
end
close drop_constraints
deallocate drop_constraints
go
--Removed CHECK Constraint-------------------------
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' --NOCHECK Constraints
print 'All Constraints Disable'
go
------------- Truncate All Tables from Model ----------------
-----To limit tables a table with sub model tables must be created and used joins-----
EXEC sp_MSForEachTable 'truncate TABLE ? '
print 'All tables truncated'
go
March 14, 2016 at 5:02 am
Please note: 8 year old thread, and the author hasn't logged in in 3 years.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply