Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2190
  • Last Modified:

Sql Server 2005: Drop all tables in a schema, then the schema too

I want to cleanup a database that has several schemas that are no longer used. I would like to get rid of them and free up some disk space. What is the best way to do this?

For example, I have a database called XYZ. Under XYZ there are several schemas such as DEV, TEST, USERDATA or whatever.

Then, under each schema, there are tables that are all identical such as:
DEV.Table1
TEST.Table1
USERDATA.Table1

I would like to remove/drop/delete DEV, TEST and USERDATA so that all the tables, objects, data get deleted returning disk space to the server.

Should I use Management Studio or a script?
0
pdi656
Asked:
pdi656
  • 3
  • 2
  • 2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Below script helps you out to drop all tables in your database.
And then you can easily delete schemas in your database which would be an easier task

EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL' 
EXEC sp_MSForEachTable 'DROP TABLE ?'

Open in new window

0
 
pdi656Author Commented:
Instead of dropping all tables in the database, I would like to drop all tables in a particular schema within the database. So in a database, you'd have SERVER.DATABASE.SCHEMA.TABLE. Then perhaps:
SERVER.SALES.DEV.Orders
SERVER.SALES.PROD.Orders
SERVER.SALES.TEST.Orders

I would like to drop DEV and TEST but not PROD.
0
 
SharathData EngineerCommented:
Then you need to try like this.

declare @Sql varchar(2000)
declare @table as table(Query varchar(2000))
set @Sql = ''
insert @table
select '['+sc.name+'].['+ta.name+']'
  from sys.tables ta 
  join sys.schemas sc on ta.schema_id = sc.schema_id
 where sc.name in ('DEV','TEST')
while @Sql is not null
begin
      set @Sql = (
      select min(Query) from @table where Query > @Sql)
      if @Sql is not null
      begin
            exec('alter table ' + @Sql + ' nocheck constraint all')
            exec('alter table ' + @Sql + ' disable trigger all')
            exec('drop table ' + @Sql) 
      end
end

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
<< I would like to drop DEV and TEST but not PROD. >>

If you want to do it specifically, then Sharath solution or an equivalent approach using cursor might help you out.

Sharath,
    In your solution Drop schema needs to be added which I found missing for the asker's requirements.
0
 
SharathData EngineerCommented:
Thanks rrjegan17 for pointing that. for the completion, here is the code.
declare @Sql varchar(2000)
declare @table as table(Query varchar(2000))
set @Sql = ''
insert @table
select '['+sc.name+'].['+ta.name+']'
  from sys.tables ta 
  join sys.schemas sc on ta.schema_id = sc.schema_id
 where sc.name in ('DEV','TEST')
while @Sql is not null
begin
      set @Sql = (
      select min(Query) from @table where Query > @Sql)
      if @Sql is not null
      begin
            exec('alter table ' + @Sql + ' nocheck constraint all')
            exec('alter table ' + @Sql + ' disable trigger all')
            exec('drop table ' + @Sql) 
      end
end
 
drop schema DEV
drop schema TEST

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Sharath,
    One more suggestion, Instead of EXEC() use EXEC sp_executesql ''

Because exec() command will be deprecated after SQL Server 2005.
0
 
pdi656Author Commented:
Thanks so much for your help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now