Solved

Delete record from multiple databases

Posted on 2009-05-06
3
237 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

910 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

21 Experts available now in Live!

Get 1:1 Help Now