Dropping Tables

Question

Post reply

Dropping Tables

scott.lucas-1068252

SSC Enthusiast

Points: 116
More actions
January 7, 2010 at 2:05 am

#386053

Hi All,
I'm quite new here and my skills on SQL are limited :/ I normally setup the server, install the database and just do the odd query when required.
At the moment I am trying to remove over 600 tables from a database that were created by error when someone connected with our software using a different owner. When we connect to the database from the software it normally creates all the tables required in the format OWNER.TABLE_NAME. The owner is normally always the same but someone has gone and connected with a different owner (this is specified in the software). This has created all the tables again with a new owner so the database has duplicate tables with different owners.
At the moment I have OWNER1.Table and OWNER2.Table. OWNER2 doesn't have any relevant data in it as these are the newly created tables.
I was wondering if there was a script I can run to delete all the tables with OWNER2. I have been on Google and tried different statements but not managed to just drop/delete these tables. The tables might have some default data in them but it is not required because everyone should be using the other tables.
I know how to drop the tables one by one but having a script would be quicker and I might learn something useful 😀
I hope this makes sense, and thank you for any help 🙂
Scott

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

So you want to drop all tables in the Owner2 schema?

Does this list all the tables that you want to drop?

SELECT s.name, t.name

FROM sys.tables t INNER JOIN sys.schemas s on t.schema_id = s.schema_id

WHERE s.name = 'Owner2'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 2

Use below script to generate drop script.

SELECT 'DROP TABLE [dbo].[' + name + ']' FROM sys.objects WHERE type='U' and schema_id = 1

-- replace dbo with your owner/schema name and replace schema_id valu with appropriate value.

Then you can execute delete statement. But if tables have dependancy on each other then it will give error. So try dropping all FKs. Use below query to generate drop statement for FK

select * from sys.all_objects where type_desc = 'FOREIGN_KEY_CONSTRAINT'

Regards,
Nitin

scott.lucas-1068252 SSC Enthusiast Points: 116 More actions · Answer 3

Hi Guys,

Thanks for the help so far...

Gail, If I run the statement I get a list of all the tables I want to drop 😀

Nitin, My SQL skills are not brilliant, I think the schema_id is 1 but not sure how I find it out. Also how do I generate the drop script? :S

Scott

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

scott.lucas-1068252 (1/7/2010)
Gail, If I run the statement I get a list of all the tables I want to drop 😀

Cool. Think your SQL skills are up to using the script I gave you to create the drop table statements by building up strings?

Hint:

SELECT 'DROP TABLE ' + s.name + ......

-- rest of my SQL statement here.

If you get stuck, post what you have.

When you get it right, you can run the select and it will print out all the DROP TABLE statements. You can then copy them to the query window, select and execute.

I strongly suggest you take a backup first. Just in case.

Nitin, My SQL skills are not brilliant, I think the schema_id is 1 but not sure how I find it out.

Schema 1 is dbo, so it's definitely not the one you want.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

toniupstny Hall of Fame Points: 3986 More actions · Answer 5

Hi. I am sure you will correct me if I am wrong here but wouldn't Nittin's statement drop all the foreign keys from all the tables including those you want to keep (owner1 vs owner2)?

if tables have dependancy on each other then it will give error. So try dropping all FKs. Use below query to generate drop statement for FK
select * from sys.all_objects where type_desc = 'FOREIGN_KEY_CONSTRAINT'

I would think you want to limit this with a where clause with more selectivity or perhaps a different query altogether??

Toni

toniupstny Hall of Fame Points: 3986 More actions · Answer 6

I believe you can use the sys.foreign_key_columns table (See BOL) to determine what schema the foreign key belongs to...

SQL Server 2008 Books Online (November 2009)
sys.foreign_key_columns (Transact-SQL)
Contains a row for each column, or set of columns, that comprise a foreign key.
Column name Data type Description
constraint_object_id
int
ID of the FOREIGN KEY constraint.
constraint_column_id
int
ID of the column, or set of columns, that comprise the FOREIGN KEY (1..n where n=number of columns).
parent_object_id
int
ID of the parent of the constraint, which is the referencing object.
parent_column_id
int
ID of the parent column, which is the referencing column.
referenced_object_id
int
ID of the referenced object, which has the candidate key.
referenced_column_id
int
ID of the referenced column (candidate key column).

Toni

scott.lucas-1068252 SSC Enthusiast Points: 116 More actions · Answer 7

Hi Everyone,

Thank you for all the posts...

I have ran

SELECT 'DROP TABLE ' + s.name + '.' + t.name

FROM sys.tables t INNER JOIN sys.schemas s on t.schema_id = s.schema_id

WHERE s.name = 'OWNER2'

This gave me a list off all the correct tables that I selected -

DROP TABLE OWNER.2.TABLENAME1

DROP TABLE OWNER.2.TABLENAME2

etc

I pasted the list into another query and ran it, all the tables were dropped 😀

I did get a few errors though, should I be worried?

Msg 3701, Level 11, State 5, Line 135

Cannot drop the table 'dbo.aspnet_Applications', because it does not exist or you do not have permission.

I am doing this in a test database at the moment.

Again Thank you, this has been really helpful 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

scott.lucas-1068252 (1/7/2010)
Msg 3701, Level 11, State 5, Line 135
Cannot drop the table 'dbo.aspnet_Applications', because it does not exist or you do not have permission.

Is that one of the tables you meant to be trying to drop? I'd check the output of the script, see if it really is doing only what it should be doing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

scott.lucas-1068252 SSC Enthusiast Points: 116 More actions · Answer 9

Not sure if it should be, I'm thinking so. I don't want anything with the table owner dbo... I can't see it under tables of the database but wasn't sure if it was a system table or something?

Scott