| Author |
Topic |
|
Zoma
Yak Posting Veteran
76 Posts |
 Posted - 2010-01-28 : 08:54:33
|
| What is T-SQL statement to truncate all tables in the database? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 08:57:14
|
[code]TRUNCATE TABLE MyTable1TRUNCATE TABLE MyTable2...[/code]this will generate the commands for you[code]SELECT 'PRINT ''Truncate ' + [name] + '''' + CHAR(13)+CHAR(10) + 'GO' + CHAR(13)+CHAR(10) + 'TRUNCATE TABLE dbo.[' + [name] + ']' + CHAR(13)+CHAR(10) + 'GO'FROM sys.tables ORDER BY [name][/code]only works for Tables owned by DBO - until you extend it  |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-01-28 : 10:09:56
|
| Here is a script to Truncate All Tableshttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341It will truncate tables that can be truncated, and delete from tables that are referenced by foreign keys, all in the correct order to avoid foreign key violations.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 10:13:06
|
| I'd forgotten about that script. That's a better idea ... |
|
|
|
Zoma
Yak Posting Veteran
76 Posts |
Posted - 2010-01-29 : 01:39:33
|
| Hi thanks all. I have truncated the tables but i did it on each single table coz i did not want to mess up or anything. Now some other tables are referenced with foreign keys and views. One that i can not get right can you be able to truncate a view coz the one that im left with it is referenced by a View in the Database |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-29 : 02:26:52
|
| Please use the script that MVJ linked to, as he explained that specifically takes care of that problem. |
|
|
|
Zoma
Yak Posting Veteran
76 Posts |
Posted - 2010-01-29 : 03:00:32
|
| Hi I tried using the script above, but there are many tables that are referenced with views and it keep failing. I see it can work. All that are left now are the ones with views and somehow it is failing to truncate!Thank you once again |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-29 : 04:32:48
|
| Is it trying to truncate the VIEWs? or do you mean it cannot truncate a table that is the target of a VIEW (which seems unlikely ... but you never know!)Edit: Checked the script, it has "Table_type='BASE TABLE'" in the where clause, so I don't think its looking at VIEWs.Personally I would change the EXECs to PRINTs to output the commands, review them, then run the script. (Ditto the Reseed command) |
|
|
|
Zoma
Yak Posting Veteran
76 Posts |
Posted - 2010-01-29 : 06:20:15
|
| The tables that were not truncated are referenced with Foreign keys and i have truncated all those dependencies,just the tables that are left un truncated they are referenced with views of those tables. In other words those views depend on these tables that is why they cannot be truncated |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-29 : 06:23:25
|
| The script would use DELETE, instead of TRUNCATE, on the tables references with Foreign Keys, so they should have been emptied (unless you have circular FKey references)I don't see how a VIEW prevents a table being truncated, but maybe there is some condition of the View that is preventing this.I would like to improve the script (for others that look for the answer to this same question), hence my interest is why it is not working for you. |
|
|
|
Zoma
Yak Posting Veteran
76 Posts |
Posted - 2010-02-01 : 02:21:56
|
| If i use the DELETE statement will it it delete the whole table or just the data in it? Coz i only delete the Data that is why i was using the Truncate method? |
|
|
|
raky
Aged Yak Warrior
767 Posts |
Posted - 2010-02-01 : 04:14:28
|
quote:
Originally posted by Zoma
If i use the DELETE statement will it it delete the whole table or just the data in it? Coz i only delete the Data that is why i was using the Truncate method?
Delete statement also only deletes the data but not removes the table from database. Drop Statement removes the table from database |
|
|
|
|
Truncating all tables