Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Drop Every Database Table - SQL Server

RSS
Modified on Wed, Apr 19, 2017, 10:48 AM by Administrator Categorized as SQL Server

Overview

The following script will generate a set of SQL statements that will drop every table in the current database. As a precursor to dropping the tables, every foreign key is dropped first.

SQL

select distinct
    DropCode = 'alter table [' + ots.name + '].[' + OnTable.name + '] drop constraint [' + fk.name + ']'

from 
    sys.foreign_keys fk

    inner join sys.objects OnTable
        on fk.parent_object_id = OnTable.object_id

    inner join sys.schemas ots
        on OnTable.schema_id = ots.schema_id

union 
select 
    DropCode = 'drop table [' + s.name + '].[' + t.name + ']'

from 
    sys.tables t
    
    inner join sys.schemas s
        on t.schema_id = s.schema_id

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.