How to delete/drop all the tables from SQL Server

If we want to delete or drop all the tables from SQL Server Database.

1) This is a hidden Stored procedure in sql server, this will be executed for each table in the database you connected. This cant be roll back.

EXEC sp_MSforeachtable @command_delete = “DROP TABLE ?”

2)If u want to delete it from the command prompt try this.

EXEC xp_cmdshell ‘SQLCMD -U -P -Q ‘EXEC sp_MSforeachtable @command_delete = “DROP TABLE ?” ‘ ,no_output

or

declare @DELETE_DROP nvarchar(max)
SELECT @DELETE_DROP = STUFF((SELECT ‘, ‘ + quotename(TABLE_SCHEMA) + ‘.’ + quotename(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name LIKE ‘%lku’
FOR XML PATH(”)),1,2,”)
SET @DELETE_DROP = ‘DROP TABLE ‘ + @SQL
PRINT @DELETE_DROP
–EXECUTE (@DELETE_DROP)

Note: This can be performed all the SQL Server versions.

Related Posts

You can leave a response, or trackback from your own site.

Leave a Reply

Powered by k2schools