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
Sys.Tables Version
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"
For more information, check here.
Related Posts:
- Inserting multiple rows in a single SQL query? [duplicate]
- SQL query to get the employee name and their manager name from the same table
- T-SQL split string
- The SQL OVER() clause – when and why is it useful?
- Update multiple columns in SQL
- SQL Server IF EXISTS THEN 1 ELSE 2
- SQL Query with SUM with Group By
- How do I perform an IF…THEN in an SQL SELECT?
- SQL update from one Table to another based on a ID match
- Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query
- SQL Server : Arithmetic overflow error converting expression to data type int
- How to calculate percentage with a SQL statement
- Query comparing dates in SQL
- What is the meaning of the prefix N in T-SQL statements and when should I use it?
- Query error with ambiguous column name in SQL
- How to drop all tables from a database with one SQL query?
- How to find sum of multiple columns in a table in SQL Server 2005?
- How can I delete using INNER JOIN with SQL Server?
- Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query
- CREATE TABLE IF NOT EXISTS equivalent in SQL Server
- How to group by month from Date field using sql
- How can I get column names from a table in SQL Server?
- Is there a combination of “LIKE” and “IN” in SQL?
- Search text in stored procedure in SQL Server
- How to format a numeric column as phone number in SQL
- SQL – WHERE (X, Y) IN (A, B)
- sql query to return differences between two tables
- SQL: IF clause within WHERE clause
- How to select the last record of a table in SQL?
- SQL- Ignore case while searching for a string
- Using Excel VBA to run SQL query
- What is tableName.* in SQL
- Selecting COUNT(*) with DISTINCT
- What is a stored procedure?
- Conversion failed when converting date and/or time from character string while inserting datetime
- When should I use CROSS APPLY over INNER JOIN?
- Insert Data Into Temp Table with Query
- error, string or binary data would be truncated when trying to insert
- How to create Temp table with SELECT * INTO tempTable FROM CTE Query
- Rename column SQL Server 2008
- Efficiently convert rows to columns in sql server
- How to delete duplicate rows in SQL Server?
- Nested select statement in SQL Server
- How do I escape a single quote in SQL Server?
- Inserting data into a temporary table
- CREATE VIEW must be the only statement in the batch
- SQL Server FOR EACH Loop
- The multi-part identifier could not be bound
- Get day of week in SQL Server 2005/2008
- How to join two tables by multiple columns in SQL?
- incorrect syntax error CREATE VIEW must be the only statement in the batch
- Conversion failed when converting from a character string to uniqueidentifier – Two GUIDs
- Find all tables containing column with specified name – MS SQL Server
- SQL Server Insert if not exists
- How can I import an Excel file into SQL Server?
- SQL Server: Invalid Column Name
- The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
- sql server invalid object name – but tables are listed in SSMS tables list
- SQL – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
- How to get a date in YYYY-MM-DD format from a TSQL datetime field?
- SQL Server IF NOT EXISTS Usage?
- String or binary data would be truncated. The statement has been terminated
- Arithmetic overflow error converting expression to data type datetime. (while displaying date time..)
- Error converting data type varchar to float
- How to concatenate text from multiple rows into a single text string in SQL Server
- How to join two tables by multiple columns in SQL?
- Count(*) vs Count(1) – SQL Server
- MS SQL compare dates?
- I want to use CASE statement to update some records in sql server 2005
- What represents a double in sql server?
- How do I fix ‘Invalid character value for cast specification’ on a date column in flat file?
- SELECT DISTINCT on one column
- TSQL PIVOT MULTIPLE COLUMNS
- Microsoft OLE DB Provider for SQL Server error ‘80004005’
- Difference between database and schema
- Why do table names in SQL Server start with “dbo”?
- SQL Server Linked Server Example Query
- Equivalent of Oracle’s RowID in SQL Server
- Subtract one day from datetime
- How do I fix the error ‘Named Pipes Provider, error 40 – Could not open a connection to’ SQL Server’?
- Replacing NULL with 0 in a SQL server query
- SQL datetime compare
- SQL Server dynamic PIVOT query?
- Backup a single table with its data from a database in sql server 2008
- Error converting data type varchar
- I want to use CASE statement to update some records in sql server 2005
- SQL Server “cannot perform an aggregate function on an expression containing an aggregate or a subquery”, but Sybase can
- Get everything after and before certain character in SQL Server
- Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
- ORDER BY items must appear in the select list if SELECT DISTINCT is specified
- how to increment integer Columns value by 1 in SQL
- What are database constraints?
- Inner Joining three tables
- SQL WHERE.. IN clause multiple columns
- Comma separated results in SQL
- SUM OVER PARTITION BY
- Filter data based on date in sql
- Varchar invalid for Sum operator
- Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF
- How to dump a Microsoft SQL Server database to a SQL script?