By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,097 Members | 887 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,097 IT Pros & Developers. It's quick & easy.

Drop all table

P: n/a
Hi everybody,

I need some help in SQL Server. I am looking for a command that will "Drop
all user table" in a
user database.

Can anyone help me?

Thank you very much
Sabrina


Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
To add to Erland's response, below is a script developed for SQL 2000
that will drop schema bound views and functions as sell as foreign keys
beforehand. This will still need to be run iteratively in the case of
nested schema bound objects.

IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
BEGIN
RAISERROR('Not for use on system databases', 16, 1)
GOTO Done
END

SET NOCOUNT ON

DECLARE @DropStatement nvarchar(4000)
DECLARE @SequenceNumber int
DECLARE @LastError int
DECLARE @TablesDropped int

DECLARE DropStatements CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
--views
SELECT
1 AS SequenceNumber,
N'DROP VIEW ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
UNION ALL
--procedures and functions
SELECT
2 AS SequenceNumber,
N'DROP PROCEDURE ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = N'FUNCTION' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0
UNION ALL
--foreign keys
SELECT
3 AS SequenceNumber,
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
N' DROP CONSTRAINT ' +
CONSTRAINT_NAME AS DropStatement
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = N'FOREIGN KEY'
UNION ALL
--tables
SELECT
4 AS SequenceNumber,
N'DROP TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
ORDER BY SequenceNumber

OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @SequenceNumber, @DropStatement
IF @@FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR('%s', 0, 1, @DropStatement) WITH NOWAIT
--EXECUTE sp_ExecuteSQL @DropStatement
SET @LastError = @@ERROR
IF @LastError > 0
BEGIN
RAISERROR('Script terminated due to unexpected error', 16, 1)
GOTO Done
END
END
END
CLOSE DropStatements
DEALLOCATE DropStatements

Done:

GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Sabrina" <mi*******@aol.com> wrote in message
news:3f*********************@read.news.de.uu.net.. .
Hi everybody,

I need some help in SQL Server. I am looking for a command that will "Drop all user table" in a
user database.

Can anyone help me?

Thank you very much
Sabrina

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.