Deleting tables from master database

Question

Post reply

Deleting tables from master database

rabisco

Ten Centuries

Points: 1032
More actions
September 24, 2010 at 2:19 pm

#227183

I have mistakenly copied a number of tables to the master database. I would like to delete them in one statement i.e. drop tbNumber*.

All the tables names are prefixed with "tbnumber"

This "drop tbNumber*" does not work though.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

each drop table statement has to be execute seperately, no wildcards, but you can generate all the necessary statements from the metadata:

then you can copy/paste the commands and run them in one quick batch.



SELECT

'DROP TABLE ' + name

from sys.tables

where name like 'tbnumber%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 2

indeed, every object needs its own drop statement.

In SSMS however, you can select all these tables in the detail pane of the tables section and delete them all in a single operation for you, but it will generate the individual commands for you.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

rabisco Ten Centuries Points: 1032 More actions · Answer 3

Lowell (9/24/2010)

each drop table statement has to be execute seperately, no wildcards, but you can generate all the necessary statements from the metadata:

then you can copy/paste the commands and run them in one quick batch.

SELECT 'DROP TABLE ' + name from sys.tables where name like 'tbnumber%'

Thanks!! that worked.

TalkSql SSC Enthusiast Points: 189 More actions · Answer 4

Hello,

I ran into the same problem, by mistake copied all the tables of Adventureworks database into the master db, I also tried to run this query but was not in much help..

SELECT

'DROP TABLE ' + name

from sys.tables

where name like ' ____ %'

Now, i get the error :3726 (cannot drop due to foreign key constraint).

How do i delete all these tables that belongs to Adventureworks which is wrongly copied under master db ?

Thanks in advance

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 5

You can do this with SSMS .

If you browse to the tables overview in the object browser and then select the object browser detail pane, you can sort on e.g. table create date and select all the tables you want to drop.

check the checkbox "continue on error" and then hit your delete button.

You will still get the error, but it will drop as much tables as it can in a single run. Just re-execute this until all selected objects nolonger exist.So you only get errormessages "unknown object".

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

Before you perform as ALZDBA Posted Today @ 2:52 PM. You might want to delete the dependent tables before the Independent tables ... to list the dependent tables execute the following: (Be sure to read the possible short cummings in the T-SQL)

/*Contains a row for each dependency on a referenced (independent) entity



as referenced in the SQL expression or statements that define some other

referencing (dependent) object.

Dependencies are established during CREATE only if the referenced (independent)

entity exists at the time that the referencing (dependent) object is created.

Due to deferred name resolution, the referenced entity need not exist at the time

of creation. In this case, a dependency row is not created.

*/

SELECT SCHEMA_NAME(o.schema_id) AS dependent_object_schema

  ,OBJECT_NAME(o.object_id) AS dependent_object_name

  ,o.type_desc AS dependent_object_type

  ,d.class_desc AS kind_of_dependency

  ,TYPE_NAME (d.referenced_major_id) AS type_name

FROM sys.sql_dependencies AS d

JOIN sys.objects AS o

  ON d.object_id = o.object_id

  AND o.type = 'U' --User Table

-- AND o.type IN ('FN','IF','TF', 'V', 'P') -- Views, functions etc

--WHERE d.class = 2 -- dependencies on types

--  AND d.referenced_major_id = TYPE_ID('<schema_name.data_type_name>')

ORDER BY dependent_object_schema, dependent_object_name;

GO

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

TalkSql SSC Enthusiast Points: 189 More actions · Answer 7

TalkSql

SSC Enthusiast

Points: 189

December 4, 2010 at 2:09 pm

#1258779

Guys,

Thanks for the help, Both of your techniques worked. I really appreciate it.

regards,

Bala