September 24, 2010 at 2:19 pm
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.
September 24, 2010 at 2:39 pm
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
September 24, 2010 at 2:44 pm
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
September 24, 2010 at 3:01 pm
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.
December 3, 2010 at 10:31 pm
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
December 4, 2010 at 12:52 pm
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
December 4, 2010 at 1:33 pm
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
December 4, 2010 at 2:09 pm
Guys,
Thanks for the help, Both of your techniques worked. I really appreciate it.
regards,
Bala
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply