Delete record from multiple databases

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
EderwainerAsked:
Who is Participating?
 
Anthony PerkinsCommented:
Something like this perhaps:

sp_msforeachdb '
USE [?]
If EXISTS (SELECT 1 FROM sysobjects where type = ''U'' And name = ''YourTableNameGoesHere'')
   DELETE YourTableNameGoesHere WHERE YourConditionGoesHere
'
0
 
RiteshShahCommented:
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
 
EderwainerAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.