Home » Blog » Topic » How to delete all tables in SQL Server database?

How to delete all tables in SQL Server database?

Stephen West ~ Modified: November 5th, 2015 ~ ~ 1 Minute Reading

Home › Forums › How to delete all tables in SQL Server database?

Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts
  • November 5, 2015 at 6:47 am #882 Score: 0
    Stephen West
    Moderator
    4 pts

    How can i delete all the tables of a database in SQL Server?

    November 6, 2015 at 11:09 am #884 Score: 0
    Andrew Jackson
    Moderator
    1 pt

    Try this query 

    declare @SQL nvarchar(max)

SELECT @SQL = STUFF((SELECT ', ' + quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME) 

FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name LIKE '%lku'
FOR XML PATH('')),1,2,'')

SET @SQL = 'DROP TABLE ' + @SQL

PRINT @SQL
 
    November 7, 2015 at 6:56 am #885 Score: 0
    Lincoln Burrows
    Moderator
    16 pts

    You should try this too 

    SELECT 'USE Serv DROP TABLE [' + SCHEMA_NAME(schema_id) + '].[' + name + ']' FROM sys.tables
SELECT ' USE Serv DROP PROCEDURE [' + SCHEMA_NAME(schema_id) + '].[' + name + ']' FROM sys.procedures
    November 9, 2015 at 7:53 am #886 Score: 0
    Stephen West
    Moderator
    4 pts

    Can I delete it form command prompt ?

    November 16, 2015 at 12:00 pm #887 Score: 0
    Lincoln Burrows
    Moderator
    16 pts

    Yes you can delete it from the command prompt try this

    EXEC xp_cmdshell 'SQLCMD -U <user> -P <password> -Q 'EXEC sp_MSforeachtable @command1 = "DROP TABLE ?" ' ,no_output
  • Author
    Posts
Viewing 5 posts - 1 through 5 (of 5 total)
  • You must be logged in to reply to this topic.