Hi all,
Recently I encountered a bizarre request, I had to delete all the tables from an Sql database.
The reason is even more bizarre, the client had a system auto creating them without making sure if they exist first, so, I had to write some code to first delete all the tables.
I thought maybe other people like me may encounter the same issue so I wrote some code for it.
The code can either print out the Drop statements for each table or execute it.
Good luck
Tested on sql server 2005
1: DECLARE @dropSql nvarchar(1000)
2: DECLARE DropSequence CURSOR LOCAL FAST_FORWARD
3:
4: FOR
5: SELECT
6: N'DROP TABLE ' + QUOTENAME(TABLE_SCHEMA) + N'.' + QUOTENAME(TABLE_NAME)
7: FROM
8: INFORMATION_SCHEMA.TABLES
9: WHERE
10: TABLE_TYPE = 'BASE TABLE'
11: AND
12: OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + N'.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
13:
14: OPEN DropSequence
15: WHILE 1 = 1
16: BEGIN
17: FETCH NEXT FROM DropSequence INTO @dropSql
18:
19: IF @@FETCH_STATUS <> 0 BREAK
20: RAISERROR (@dropSql , 0, 1) WITH NOWAIT
21:
22: --EXEC(@dropSql )
23: PRINT @dropSql
24: END
25:
26: CLOSE DropSequence
27: DEALLOCATE DropSequence
My name is Avi Tzurel. I'm a professional web developer from Israel. I spend most of my day developing both web products and RIA applications as well as imparting my experience onto others. I speak, teach and write about my passions, and develop applications according to what I preach. I specialize in Flex, Adobe Air, HTML, XHTML, Javascript, jQuery and other Javascript libraries, on the server side I do .net along side with Ruby on Rails. You can connect with me on Twitter or email me through the contact page on this blog.
Great articles & Nice a site!!