Drop all tables for a given schema... 1

[Neil Toulouse]

Hi guys!

Is there a quick way in T-SQL of dropping all the tables that are associated with a particular schema? I originally set about changing the ownership instead to [dbo], but realised they already existed in that schema, so I just need to delete them.

TIA
Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[gmmastros]

Run this in a query window:

Select 'Drop Table [' + Table_Schema + '].[' + Table_Name + ']' 
From   Information_Schema.Tables 
Where  Table_Type = 'Base Table'
       And Table_Schema = '[!]YourSchemaNameHere[/!]'

This will generate a list of drop table commands that you can copy/paste in to another query window.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Neil Toulouse]

Nice one George, that will do nicely

I like work. It fascinates me. I can sit and look at it for hours...