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 |
|
OldMySQLUser
Constraint Violating Yak Guru
301 Posts |
 Posted - 2008-12-02 : 04:33:10
|
| I need to bring my development database into line with my colleague.How can i drop all the tables in a database while retaining the existing stored procedures/views/functions etc please? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-02 : 04:38:12
|
sp_msforeachtable 'PRINT ''?'''sp_msforeachtable 'DROP ?'
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-02 : 04:43:29
|
| But wont this cause sp's,functions,views etc using these tables to fail? or are you planning to recreate them afterwards? |
|
|
|
OldMySQLUser
Constraint Violating Yak Guru
301 Posts |
Posted - 2008-12-02 : 04:57:21
|
| I'm going to recreate the dropped functions/views afterwards.DO I just exec:use myDatabaseexec sp_msforeachtable 'PRINT ''?'''sp_msforeachtable 'DROP ?'is this correct? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-02 : 04:59:34
|
use myDatabaseexec sp_msforeachtable 'PRINT ''?'''exec sp_msforeachtable 'DROP TABLE ?'Views, function and SPs are preserved. Triggers are deleted.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
OldMySQLUser
Constraint Violating Yak Guru
301 Posts |
Posted - 2008-12-02 : 05:14:59
|
| Strangely this did not work for me. My SQL Server 2005 tables were still intact after the script had completed.Looks like I will have to save the procedures/views/etc to a text .sql file and edit them in by hand later. :( |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-02 : 05:34:29
|
If you have referential constraints in place, you will have to run the code several times.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Drop all tables and keep all stored procs?