How to drop all tables from a database with one SQL query

0 votes
I don't want to type all tables' name to drop all of them. Is it possible with one query?
Feb 4 in Database by Vaani
• 7,020 points
1,259 views

1 answer to this question.

0 votes

Use the INFORMATION_SCHEMA.TABLES view to get the list of tables. Generate Drop scripts in the select statement and drop it using Dynamic SQL:

DECLARE @sql NVARCHAR(max)=''

SELECT @sql += ' Drop table ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + '; '
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'

Exec Sp_executesql @sql

DECLARE @sql NVARCHAR(max)=''

SELECT @sql += ' Drop table ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + '; '
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U'

Exec sp_executesql @sql

Note: If you have any foreign Keys defined between tables then first run the below query to disable all foreign keys present in your database.

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
answered Feb 4 by Neha
• 8,940 points

Related Questions In Database

0 votes
0 answers
0 votes
0 answers

SQL TRUNCATE DATABASE ? How to TRUNCATE ALL TABLES

SQL Server 2008 R2 is what I ...READ MORE

Aug 18 in Database by Kithuzzz
• 20,660 points
124 views
0 votes
0 answers

How to make a query with group_concat in sql server ?

I am aware that the Group Concat ...READ MORE

Aug 28 in Database by Kithuzzz
• 20,660 points
150 views
0 votes
0 answers

How to create a table from select query result in SQL Server 2008

I tried to build a table from ...READ MORE

Sep 2 in Database by Kithuzzz
• 20,660 points
95 views
0 votes
1 answer

Calculate Time Intersection to Correlate Sequences of Independent Events

I think this solution requires a CROSS JOIN implementation. ...READ MORE

answered Oct 26, 2018 in Power BI by Upasana
• 8,620 points
348 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3 in Database by Vaani
• 7,020 points
169 views
0 votes
0 answers

How do I UPDATE from a SELECT in SQL Server?

INSERT INTO Table (col1, col2, col3) SELECT col1, ...READ MORE

Feb 4 in Database by Vaani
• 7,020 points
72 views
0 votes
1 answer

What is a stored procedure?

A stored procedure is a set of ...READ MORE

answered Feb 4 in Database by Neha
• 8,940 points
304 views
0 votes
1 answer

How to rename a column in a database table using SQL?

For SQL Server, use sp_rename USE AdventureWorks; GO EXEC sp_rename 'Customers.CustomerTerritory.TerritoryID', ...READ MORE

answered Feb 9 in Database by Neha
• 8,940 points
149 views
0 votes
1 answer

How to do version control for SQL Server database?

Not only can you compare objects at ...READ MORE

answered Feb 15 in Database by Neha
• 8,940 points
87 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP