Easiest way to drop all tables ...

Hi

I am having 'n' number of tables within a database. There are relationships
set between tables.

Now I am writing cleanup scripts for the database. i.e., when a need araises
i should be in a position to remove all tables from the database and then
rerun my create table scripts. Can anyone tell me how to write the drop table
tablename for all tables in a easier way?

Regards
Pradeep

Easiest way to drop all tables ...

Hi
DECLARE @DropStatement nvarchar(4000)
DECLARE DropStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'DROP TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @DropStatement
IF @@FETCH_STATUS <> 0 BREAK
RAISERROR (@DropStatement , 0, 1) WITH NOWAIT
--EXEC(@DropStatement )
PRINT @DropStatement
END
CLOSE DropStatements
DEALLOCATE DropStatements

Easiest way to drop all tables ...

On Jan 29, 11:28 am, SqlBeginner

If you want to remove *all* tables, it's easier to just delete the
database and create it anew. If you're unwilling to do that, the
following script might help, but *WARNING* *WARNING* *WARNING* this
script removes all user tables from a database:

declare boris cursor for
select
'alter table [' + USER_NAME(so.uid) + '].[' + OBJECT_NAME(sfk.fkeyid)
+ '] drop constraint [' + OBJECT_NAME(sfk.constid) + ']'
from
sysforeignkeys sfk
inner join
sysobjects so
on
sfk.fkeyid = so.id
where
OBJECTPROPERTY(so.ID,N'IsMSShipped') = 0 and
OBJECTPROPERTY(so.ID,N'IsTable') = 1

declare @sql varchar(8000)

open boris

fetch next from boris into @sql
while @@FETCH_STATUS = 0
begin
exec(@sql)

fetch next from boris into @sql
end

close boris
deallocate boris

declare boris cursor for
select
'drop table [' + USER_NAME(uid) + '].[' + OBJECT_NAME(ID) + ']'
from
sysobjects
where
OBJECTPROPERTY(ID,N'IsMSShipped') = 0 and
OBJECTPROPERTY(ID,N'IsTable') = 1

open boris

fetch next from boris into @sql
while @@FETCH_STATUS = 0
begin
exec(@sql)

fetch next from boris into @sql
end

close boris
deallocate boris

Damien

Thanks for the query. Can't we avoid using cursors?

Thanks for the query. Can't we avoid using cursors? I was just wondering
whether we can make use of sp_ForeachTable or somethign like that!

Regards
Pradeep

Yep, but be aware that this SP is not ducumented/unsupported so I'd not use it

Yep, but be aware that this SP is not ducumented/unsupported so I'd not use
it in producation enviroment at least


EXEC sp_msForEachTable @COMMAND1= 'delete from ?'

Easiest way to drop all tables ...

On Jan 29, 1:04 pm, SqlBeginner
You can avoid it using sp_MsForEachTable or sp_execresultset, but
these are undocumented procedures, as Uri said, and IIRC are only
usable by sa users (so even if you're dbo of your database, if you're
not sa on the server you can't use them). Plus, while you can use the
sp_MsForEachTable to drop the tables, getting the dependencies right
can be a real pain (which is why my script dropped the foreign keys
before attempting to drop the tables)

Damien

Pradeep,I have seen this failing if you have forign key constraints.

Pradeep,

I have seen this failing if you have forign key constraints. So best option
is to use a script or recreate the database as a whole.

THanks
Hari

Easiest way to drop all tables ...

That's why the script I presented drops all of the Foreign Keys first.
Guess I should have highlighted that. (Also should point out that to
do a thorough job, should drop stored procs, then views, then fks and
then tables. Didn't do SPs/Views in the previous script, but easy
enough to add)

Damien

SqlBeginner,Yes we can.drop database your_db;gocreate database your_dbon ...

SqlBeginner,

Yes we can.

drop database your_db;
go

create database your_db
on ...
go


AMB

Hi Pradeep,As others suggested i guess you can go ahead with "Dropping" and

Hi Pradeep,

As others suggested i guess you can go ahead with "Dropping" and

If at all you still want to continue this way :) just check out
http://vadivel.blogspot.com/2006/07/easiest-fastest-way-to-delete-all.html I
have provided a way by which you can make use of undocumented stored proc to
delete records from all tables within the DB. You can change the logic
according to your need using that code.

Hope this helps!

Best Regards
Vadivel

http://vadivel.blogspot.com

Remove all tables with given scripts then take a backup.

Remove all tables with given scripts then take a backup. When you want to
back to old state just restore db.

How to without cursors or undocumented procs

Pradeep, there is absolutely a way to do this without cursors or undocumented stored procedures. Please see my blog article to see how.

http://blog.falafel.com/2009/01/06/TSQLDropAllObjectsInASQLServerDatabase.aspx

How to without cursors or undocumented procs

Pradeep, there is absolutely a way to do this without cursors or undocumented stored procedures. Please see my blog article to see how.

http://blog.falafel.com/2009/01/06/TSQLDropAllObjectsInASQLServerDatabase.aspx

Easiest way to drop all tables

SELECT name INTO #tables from sys.objects where type = 'U'

while (SELECT count(1) FROM #tables) >= 0

begin

declare @sql varchar(max)

declare @tbl varchar(255)

SELECT top 1 @tbl = name FROM #tables

SET @sql = 'drop table ' + @tbl

exec(@sql)

DELETE FROM #tables where name = @tbl

end

DROP TABLE #tables;

Thanks

You are required to be a member to post replies.  After logging in or becoming a member, you will be redirected back to this page.

Drop all the tables from SQL Server

Hi,



Try this :--



EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"



This is a hidden stored Procedure in sql server, this will be executed for each table in the database you connected (you cant rollback this). If there is Relation in tables then execute multiple times.