Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Delete record from multiple databases

Posted on 2009-05-06
3
Medium Priority
?
249 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

604 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