This is a really handy little script but there is a bug.

The "count(1)" I think should be "count(*)"... count(1) will always equal 1.

Thanks,
Ian Beckett



Manish Srivastava wrote:

Easiest way to drop all tables
20-Feb-09

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;

Previous Posts In This Thread:

On Monday, January 29, 2007 6:28 AM
SqlBeginne wrote:

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

On Monday, January 29, 2007 6:39 AM
Uri Dimant wrote:

Re: 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

"SqlBeginner" <SqlBeginner (AT) discussions (DOT) microsoft.com> wrote


On Monday, January 29, 2007 6:44 AM
Damien wrote:

Re: Easiest way to drop all tables ...
On Jan 29, 11:28 am, SqlBeginner
<SqlBegin... (AT) discussions (DOT) microsoft.com> wrote:

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

On Monday, January 29, 2007 8:04 AM
SqlBeginne wrote:

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

"Damien" wrote:

On Monday, January 29, 2007 8:10 AM
Uri Dimant wrote:

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 ?'

On Monday, January 29, 2007 8:20 AM
Damien wrote:

Re: Easiest way to drop all tables ...
On Jan 29, 1:04 pm, SqlBeginner
<SqlBegin... (AT) discussions (DOT) microsoft.com> wrote:
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

On Monday, January 29, 2007 8:40 AM
Hari Prasad wrote:

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

On Monday, January 29, 2007 9:02 AM
Damien wrote:

Re: Easiest way to drop all tables ...
On Jan 29, 1:40 pm, "Hari Prasad" <hari_prasa... (AT) hotmail (DOT) com> wrote:
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

On Monday, January 29, 2007 9:47 AM
AlejandroMes wrote:

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



"SqlBeginner" wrote:

On Monday, January 29, 2007 10:15 AM
Vadive wrote:

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
"Recreating" the database with ease.

If at all you still want to continue this way just check out
http://vadivel.blogspot.com/2006/07/...elete-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


"SqlBeginner" wrote:

On Monday, January 29, 2007 10:16 AM
italic wrote:

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.

On Tuesday, January 06, 2009 2:48 PM
Adam Anderson wrote:

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/T...rDatabase.aspx

On Tuesday, January 06, 2009 2:48 PM
Adam Anderson wrote:

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/T...rDatabase.aspx

On Friday, February 20, 2009 1:03 AM
Manish Srivastava wrote:

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;

On Saturday, May 30, 2009 5:59 AM
venkateshkumar p wrote:

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.


Submitted via EggHeadCafe - Software Developer Portal of Choice
Spambot Killer ASP.NET Mailto: Hyperlink Control
http://www.eggheadcafe.com/tutorials...aspnet-ma.aspx

(Ian Beckett) writes:
COUNT(*) or COUNT(1) returns the same.

However "SELECT COUNT(col) FROM tbl" may return a different value. To
wit, only non-NULL values are counted.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

who do i contact to have this thread taken down? there is no contact info,
and this was scraped from another source without permission.

thanks

"unknown" wrote:

>

How about an alternative, like this:

DECLARE @x nvarchar(max);
WITH CTE
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY object_id) AS ID,
'DROP TABLE [' + SCHEMA_NAME(schema_id) + '].[' + name + '];' AS
table_name
FROM sys.objects
WHERE type = 'U'
)
SELECT @x =
CAST((
SELECT table_name AS '*'
FROM CTE
FOR XML PATH('')
) AS nvarchar(max));
EXEC (@x);

Feel free to use this however you like. Notice we can get rid of the temp
table, the WHILE loop, add support for schemas, and get rid of the DELETE
statement all in one shot.

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"Ian Beckett" wrote in message news:200912217857ibeckett (AT) gmail (DOT) com...