| Author |
Topic |
|
PA123
Starting Member
11 Posts |
 Posted - 2006-09-26 : 12:09:29
|
| Hi there,Apologies firstly if i'm in the wrong forum.I've come across a little problem is sql2005. I would like to know if its possible to delete all records, from all the tables in my database. I basically want to reset all 320 tables to empty, without deleting them. I've looked at variants of the delete and truncate commands, but can't find a specific command for it.Thanks in advance.PJA |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
PA123
Starting Member
11 Posts |
Posted - 2006-09-26 : 13:33:14
|
| Hi, thanks for that. Is that a reliable way of doing it? I guess there isn't a Delete All function or script which does the same thing in essence? |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-09-26 : 14:07:55
|
| But why would you bother....Assuming you've got RI on...then you need to make sure that you do the DELETES in the correct order...and Deletes would take longer because it would have to log every deelete.And if you do have RI TRUNCATE is out the window, because you can't Truncate with RI...so taht means if you want to use Truncate you'd have to DROP all of the RI....So why not do thatAlso, never mind about dropping the database, just do thisScript the Database, Backup the database, create a NEW database and create the script in there.Now you have an empty database for any future use...put it in READONLY mode...Dump the readonly database and restore it over the existing databaseviolaBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-26 : 14:29:59
|
| I agree with Brett. This is what I do when we "mess up" in development. Deleting the data would just take too long.Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-26 : 14:33:56
|
"Dump the readonly database ..."Oldspeak for "Backup the database" not "Throw it in the trash"  ... just in case that wasn't obvious. |
|
|
|
PA123
Starting Member
11 Posts |
Posted - 2006-09-26 : 15:00:56
|
| Guys, thanks for that. I really appeciate the help. I'll give that a go and see what happens. Incidentally, would i need to script all the objects in the database? What if i just seleced the database at the highest (top) level, and scripted that - would it also incorporate all the objects underneath it (for that particular branch, if you will)? So tables, functions, views etc?Thanks again. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-26 : 15:07:02
|
| Regardless of where you enter the Generate SQL script wizard, once in it, select the objects that you want to script.Tara Kizer |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-09-26 : 15:14:56
|
| You sound like you will need to script the entire database (I would).I would create all the tables first and leave the RI to the last thing that get's created. That way you don't need to worry about the order of things.Make sure you select all options, even if you don't think you need them.You are probably going to need to worry about LoginsBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-26 : 15:22:58
|
Whilst I agree with the others that creating a Vanilla Database is the best approach if you want to try TRUNCATE & DELETE this may help.Run the script to "generate" a Truncation & Deletion script.Run the TRUNCATE part of the resulting script to quickly truncate any tables that do not have any Referential integrity.The run the DELETE part of the script repeatedly until you no longer get any errors (e.g. Foreign Key Violation errors)There is a COUNT section int eh script too - if you want to check Row Counts in tables.(Only works if all tables owned by DBO) Looks like it actually handles that correctly!PRINT '----- TRUNCATE TABLE SECTION -----'SELECT 'PRINT ''[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']''' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 'TRUNCATE TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' + CHAR(13) + CHAR(10) + 'GO'FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT IN ('dtproperties')ORDER BY TABLE_NAME--PRINT ''PRINT '----- DELETE ROW SECTION -----'SELECT 'PRINT ''[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']''' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 'DELETE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' + CHAR(13) + CHAR(10) + 'GO'FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT IN ('dtproperties')ORDER BY TABLE_NAME--PRINT ''PRINT '----- COUNT(*) SECTION -----'SELECT 'SELECT COUNT(*), ''' + TABLE_SCHEMA + '.' + TABLE_NAME + ''' FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT IN ('dtproperties')ORDER BY TABLE_NAMEKristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-26 : 15:31:47
|
| "you can't use TRUNCATE With RI on the tables"Indeed, but the script puts a GO after each command, so all the RI tables will fail the truncate and give an error, but the others will Truncate OK; then the repeated use of the DELETEs will, eventually, get rid of all the data in the RI tables.But its a crap way to do it, for sure!, but Cheap & Cheerful nonetheless.Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-26 : 15:42:36
|
| The GOs don't make a difference in this situation. GOs are needed for situations where a certain command must be the first in a batch.Tara Kizer |
|
|
|
PA123
Starting Member
11 Posts |
Posted - 2006-09-26 : 15:58:55
|
| Hello Guys,Tentatively, i tried the database scripting approach, and everything seems fine apart from some RI constraint/rule infringements. It is possible to explicitly set the order of creation in the scripting wizard so that RI is left till the end? Maybe, copy it over separately? What's the best approach?X002548 - you mentioned "You are probably going to need to worry about Logins". You've got me a little worried, truthfully i'm not exactly sure what you mean, or how this could impact. I'm assuming user access rights are left intact/copied across? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-26 : 16:09:49
|
| User access rights are only added if you select that option in the wizard. The wizard scripts things according to sysdepends. There is no way to force the wizard to script in a different order. It would have to be done manually.Tara Kizer |
|
|
|
PA123
Starting Member
11 Posts |
Posted - 2006-09-26 : 16:38:25
|
quote:
Originally posted by tkizer
User access rights are only added if you select that option in the wizard. Tara Kizer
So user rights will be preserved if defined during the wizard configuration?quote:
Originally posted by tkizer
The wizard scripts things according to sysdepends. There is no way to force the wizard to script in a different order. It would have to be done manually.
Therefore, i'm advised to copy over RI contraints manually? <gulp>.....I attempted to restore the scripted DB over the original DB, and despite it saying "restore completed", "success" etc, the original data is still in the tables. Would there be something obvious i'm missing? During the restore process "overwrite" was ticked. Everything glaringly obvious (well to me anyway) that needed to be ticked was.Sorry for bombarding you guys like this  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-26 : 16:42:49
|
| No you aren't advised to copy them over manually. But you can modify the script that the wizard generates. As long as you select the proper things in the wizard, your database should look the same afterwards just with no data in it. Doing a restore does not empty out the tables.Tara Kizer |
|
|
|
PA123
Starting Member
11 Posts |
Posted - 2006-09-26 : 16:58:42
|
quote:
Originally posted by tkizer
No you aren't advised to copy them over manually. But you can modify the script that the wizard generates. As long as you select the proper things in the wizard, your database should look the same afterwards just with no data in it. Doing a restore does not empty out the tables.Tara Kizer
Tara, a restore doesn't empty out the tables, even if the source DB (the scripted one) is empty? That's all i want at the end of the day, is a blank database indentical to the original.Maybe i'm not understanding correctly. Picture this if you may - original database, lots of tables and consequently lots of data too. So, script original database using wizard, create new database from script. Save both DB's.....now restore original DB from the scripted one (which has nothing in it). From that my understanding was, i'd have an empty database. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-26 : 17:03:52
|
| If the source is empty, then once restored it will be empty. It looks the same way as it did upon the backup.You don't need to do backup/restore though to get an empty database. Tara Kizer |
|
|
|
PA123
Starting Member
11 Posts |
Posted - 2006-09-26 : 17:12:59
|
quote:
Originally posted by tkizer
If the source is empty, then once restored it will be empty. It looks the same way as it did upon the backup.You don't need to do backup/restore though to get an empty database. Tara Kizer
In layman's terms (it's late, i want to go home - i'm probably not making much sense), am i not restoring the empty DB (scripted one) over the top of the original DB (with data in it). After which, shouldn't the original DB be empty? |
|
|
|
Next Page
|
How do i delete all rows from all tables.