?
Solved

Delete record from multiple databases

Posted on 2009-05-06
3
Medium Priority
?
247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 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 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

771 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