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 |
|
shapper
Constraint Violating Yak Guru
450 Posts |
 Posted - 2008-02-26 : 18:40:19
|
| Hello,To delete all tables from my database I use:SELECT 'DROP table ' + table_NAMEFROM INFORMATION_SCHEMA.tablesTo get the list of the tables. Then I run that list:DROP table Folders DROP table Files ...Because of relationships I need to run it various times until all the tables disappear. Is there a way to everything in one command?Thank You,Miguel |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-02-26 : 18:44:46
|
You could try something like thisWHILE EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES) EXEC sp_msforeachtable 'drop table ?'
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
shapper
Constraint Violating Yak Guru
450 Posts |
Posted - 2008-02-26 : 19:11:23
|
| It worked fine.Just one more thing. Is it possible to do the same for the functions and triggers?For functions and procedures I am trying the followingWHILE EXISTS (SELECT * FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_TYPE = 'FUNCTION') EXEC sp_msforeachfunction 'drop function ?'For procedures I would use "procedure" as routine type.I know this isn't working because I there is no such procedure as sp_msforeachfunction.How could I do it?Probably just deleting all routines?And where can I find information about the procedures you are using:sp_msforeachtable, ...?Thank You,Miguel |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-26 : 21:25:52
|
| Sp_msforeachtable is non-documented, and you can use cursor to drop functions and triggers. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Delete Tables