Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pjstreiff
Starting Member
12 Posts |
 Posted - 2007-10-26 : 15:07:18
|
| Currently using this script to truncate all tables belonging to specified schema:EXEC sp_MSforeachtable ' IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1 DELETE FROM ? else TRUNCATE TABLE ?'GOdeclare @RETURN_VALUE intdeclare @command1 nvarchar(2000)declare @whereand nvarchar(2000)set @command1 = 'truncate table ?'set @whereand = 'and uid = ''7''' -- arch schema = 7use Database_name exec @RETURN_VALUE = sp_MSforeachtable @command1=@command1, @whereand = @whereandThe only problem is that it requires that I find the ID of the schema which makes it manual and not very 'portable'. Not to mention, error prone.Is there a way to do something similiar by passing a schema NAME parameter instead?Thanks in advance,Phil Streiff, MCDBA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-26 : 15:11:52
|
| Moving this out of the Script Library since it is not a fully functional script yet.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Improve this Truncate script please?