Stored procedure to delete a table

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored procedure to delete a table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-05-06 : 08:17:57

logesh writes "how to write a stored procdeure to delete tables in the database"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-05-06 : 08:20:11

Do you mean delete rows from tables, or dropping the tables entirely?

In either case, you should not write something that can accept a table name and then do that action on that table. It's a significant security risk and could easily destory a database by accident.

More information on what you're trying to do would be helpful.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-06 : 08:20:52

Look for Stored Procedures, Dynamic SQL, Bad Ideas, in BOL

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-06 : 08:53:43

[code]
CREATE PROCEDURE mwahaha
AS

DECLARE @tables TABLE(ident INT IDENTITY(1,1), tablename VARCHAR(256))

DECLARE
@tablename VARCHAR(256),
@counter INT,
@max INT,
@sql VARCHAR(4000)

INSERT @tables(tablename)
SELECT name FROM sysobjects WHERE xtype = 'U'

SELECT
@counter = 1,
@max = (SELECT MAX(ident) FROM @tables)

WHILE @counter <= @max
BEGIN

SELECT @tablename = (SELECT tablename FROM @tables WHERE ident = @counter)
SELECT @sql = 'DROP TABLE ' + @tablename
PRINT @sql
-- EXEC(@sql) --You sure you want to run this???

SELECT @counter = @counter + 1
END
[/code]

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-06 : 12:43:04

Wouldnt DROP DATABASE be easier?

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-06 : 12:48:04

Hey, when did they change your title?

In any event, contraints will cause some to fail...you have to keep running it until they're all gone......

DROP DATABASE myBD99

Would be much easier....

Brett

8-)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-05-06 : 20:17:42

Hell, while we're talking about bad ideas, try this one:

TRUNCATE TABLE master..sysdatabases

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources