Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Truncate All Tables Expand / Collapse
Author
Message
Posted Monday, November 17, 2008 2:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 30, 2011 1:00 AM
Points: 14, Visits: 17
/*I Do It As Follows:*/
DECLARE @ROWCOUNT INT
DECLARE @sSQL NVARCHAR(2000)
CREATE TABLE #DropTableNames(
TableName VARCHAR(30),
TableID INT IDENTITY(1,1) not null)
INSERT INTO #DropTableNames(TableName)
SELECT Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Type='BASE TABLE'
SET @ROWCOUNT=@@ROWCOUNT
WHILE @ROWCOUNT>0
BEGIN
SELECT @sSQL='TRUNCATE TABLE '+TableName FROM #DropTableNames WHERE TableID=@ROWCOUNT
SET @ROWCOUNT=@ROWCOUNT-1
EXEC SP_EXECUTESQL @sSQL
END
/*My English is poor So ...Here Is No Marker*/
Post #603518
Posted Monday, November 17, 2008 5:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:55 PM
Points: 137, Visits: 251
If not references are present on the tables:
EXEC sp_MSForEachTable 'truncate TABLE ? ' will be enough to truncate all tables.
the sp_MSForEachTable replace the ? for the actual table Name.
Post #603577
Posted Monday, November 17, 2008 5:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:55 PM
Points: 137, Visits: 251
You can join the table with the names of tables also the statement.
Post #603580
Posted Monday, November 17, 2008 5:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 26, 2015 6:44 AM
Points: 329, Visits: 476
Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/truncate-all-tables-part-ii.aspx




Madhivanan

Failing to plan is Planning to fail
Post #603584
Posted Monday, November 17, 2008 3:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:55 PM
Points: 137, Visits: 251
Very good information on the link. Like the Blog stated there are many ways to aproach and problem..Thanks for the input.
Post #603978
Posted Thursday, October 28, 2010 7:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 7:36 AM
Points: 5, Visits: 22
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
Post #1012275
Posted Thursday, October 28, 2010 7:28 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Saturday, October 24, 2015 2:31 AM
Points: 3,158, Visits: 11,771
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
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341






Post #1012297
Posted Thursday, June 2, 2011 4:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 21, 2016 4:59 PM
Points: 226, Visits: 665
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.
Post #1118627
Posted Monday, March 14, 2016 4:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 7, 2016 3:25 AM
Points: 2, Visits: 73
Can explain me why you create the table T_FK_Xref?

For rollback?
Post #1769023
Posted Monday, March 14, 2016 4:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 7, 2016 3:25 AM
Points: 2, Visits: 73
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
Post #1769028
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse