Truncate All Tables
-
Comments posted to this topic are about the item Truncate All Tables
-
-
What's wrong with this:
EXEC sp_MSforeachtable @command1 = 'alter table ? nocheck constraint all'EXEC sp_MSforeachtable @command1 = 'delete from ?'EXEC sp_MSforeachtable @command1 = 'alter table ? check constraint all' -
The proper syntax should be:
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
where the ? is been replace for the table name
-
What about if all you want is to give an empty shell to a developers groups or you want to do a bulk insert into multiple tables?
-
Hi Jorge
Its' good.
This works for me.
Where is the part 2?
Appreciated !!!
-
Hi Jorge
Could you please the 2nd part of this too.
I am working on similar thing, and i am sure your code 'll help me a lot.
Thanks in advance !
-
Here it is:
--------------4. Restore Relationships. ON delete NO action to be research ------------
/* Restore relationships for delete set to no action; for update set to no action as on
original reverse eng. model
*/
declare @ChildTable varchar (max) -- Child table Name
declare @ChildColumn varchar(max)-- Child column Name
declare @MasterTable varchar (max) -- Master TAble
declare @MasterColumn varchar (max) -- Master Column reference
declare @sqlcmd varchar (max) -- Sql Command
declare @ConstraintName varchar(max) -- Constraint Name
declare ADD_Constraint cursor
fast_forward for
select distinct ConstraintName,ChildTable,ChildColumn,MasterTable,MasterColumn
from [CoDE].[dbo].t_FK_Xref order by ConstraintName
open ADD_Constraint
fetch next from ADD_Constraint
into @ConstraintName,
@ChildTable,
@ChildColumn,
@MasterTable,
@MasterColumn
while @@Fetch_Status = 0
begin
begin try
select @sqlcmd = 'alter table '+@ChildTable+' with nocheck add constraint '+@ConstraintName+' foreign key '+'('+@ChildColumn+')'+' references '+@MasterTable+' ('+@MasterColumn+')'+' on delete no action on update no action'
exec (@sqlcmd)
fetch next from ADD_Constraint
into @ConstraintName,
@ChildTable,
@ChildColumn,
@MasterTable,
@MasterColumn
end try
begin catch
print @sqlcmd+' ***** Error checkpoint '
fetch next from ADD_Constraint
into @ConstraintName,
@ChildTable,
@ChildColumn,
@MasterTable,
@MasterColumn
end catch
end
close ADD_Constraint
Deallocate ADD_Constraint
go
---------------5. Restore CHECK Constraints---------------
-- Now enable referential integrity again
--EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
print 'Constraints Restored'
-
I will gladly help you if you write about what are you trying to do. There are many uses for the script such as inserting into a bulk insert, deleting a child table, etc...
-
Though it was little late
But i appreciate your help !
Thanks a lot Jorge !!
I'll let you know if i need more help
-
/*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*/
-
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.
-
You can join the table with the names of tables also the statement.
-
Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/truncate-all-tables-part-ii.aspx
MadhivananFailing to plan is Planning to fail
-
Very good information on the link. Like the Blog stated there are many ways to aproach and problem..Thanks for the input.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply