Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ngk
Starting Member
10 Posts |
 Posted - 2008-11-26 : 06:44:46
|
| Hi there, can this be done - here is the subquery that I'd like to use :SELECT nameFROM dbo.sysobjectsWHERE (xtype = 'U' AND name LIKE 'd_ord%') AND (name NOT LIKE 'D_Ord_Act_Type')Can this be incorporated into a Delete From clause? if not there is only likely to be less than 100 tables fitting the criteria so I guess a loop would be acceptable too, thank-you! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-26 : 07:03:20
|
| Nope you cant delete from sys.objects table.Why are you trying to do this?is your intention to delete the tables themselves? |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-26 : 07:07:31
|
| Is there certain schema and tablename that you want to drop? You can use sp_Msforeachtable and choose certain schema. |
|
|
|
ngk
Starting Member
10 Posts |
Posted - 2008-11-26 : 07:15:27
|
Hi no, I want to delete from the tables themselves but I have given the select as an example of what I'm trying to do - I want to delete all rows from the tables with a prefix of 'd_ord', instead of doing individual delete statements for each of them - is this possible, even with a loop ?  |
|
|
|
ngk
Starting Member
10 Posts |
Posted - 2008-11-26 : 07:18:14
|
| I've not used - sp_Msforeachtable - i can look this up, or can you give me quick example? please |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-26 : 07:27:40
|
| Things get messy if you have foreign keys and constraints.Look at this one from Madhi:(Make sure to filter what you want and Run select before deleting)http://sql-server-performance.com/Community/forums/p/20593/114949.aspx |
|
|
|
ECS_Dale
Starting Member
5 Posts |
Posted - 2008-11-26 : 07:52:54
|
| I would use the select statement you gave to create a cursor. Then use the cursor to generate the delete statement in a loop.Watch out for foreign keys though and other constraints. |
|
|
|
ngk
Starting Member
10 Posts |
Posted - 2008-11-26 : 09:49:56
|
| Thank-you all for the help - think I will go with the sp_msforeachtable and foreign keys shouldn't be an issue where these tables are concerned |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-26 : 20:03:36
|
| Then use this:(This will be faster)Run the output:select 'Truncate table' + char(13) + name from sys.sysobjectsWHERE (type = 'U' AND name LIKE 'd_ord%') AND (name NOT LIKE 'D_Ord_Act_Type') |
|
|
|
|
|
|
Delete from Multiple Tables