Solved

Delete record from multiple databases

Posted on 2009-05-06
3
234 Views
Last Modified: 2012-05-06
Hi,
I'm system administrator of an instance of SQL Server Express database running in a dedicated server with over 400 databases on it and I was wondering if it's possible to delete one particular record on if not all, on most of these databases at once, as all of them have the same structure, tables and fields.

I have SA permission over it and deleting the records one at a time would take me a lot of time.

Thanks!

Eder
0
Comment
Question by:Ederwainer
3 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 125 total points
ID: 24321440
Something like this perhaps:

sp_msforeachdb '
USE [?]
If EXISTS (SELECT 1 FROM sysobjects where type = ''U'' And name = ''YourTableNameGoesHere'')
   DELETE YourTableNameGoesHere WHERE YourConditionGoesHere
'
0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 125 total points
ID: 24322127
since you are trying to perform operation on mutliple databases on the server, it would be great to use undocumented stored procedure "Sp_MsForEachDB", it would be very helpful in your case, ACPERKINS gave you perfect solution, if you would like to see more about this SP and its usage, have a look at my small script at

http://www.sqlhub.com/2009/04/find-user-in-all-database-with.html


BTW, you can use below script also which will not query system databases.




EXEC sp_MSforeachdb

'if ''?'' <> ''tempdb'' and ''?'' <> ''master'' and ''?'' <>''model'' and ''?''<>''model''

begin

USE [?]

If EXISTS (SELECT 1 FROM sysobjects where type = ''U'' And name = ''YourTableNameGoesHere'')

   DELETE YourTableNameGoesHere WHERE YourConditionGoesHere

end

'

Open in new window

0
 

Author Closing Comment

by:Ederwainer
ID: 31578813
Absolutely helpfull!

Although I'm a total newbye at SQL I feel at home and confident here with the level of professionals enganged in helping others.

Thank you all!

Eder Wainer
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now