January 6, 2003 at 1:49 pm
I have a database that was created by doing a copy of another database. In that DB I have over 2000 tables with 3 different owners ( dbo, PRODCTRL, PRODDATA ). I want to change all tables with the owner PRODCTRL to TESTCTRL ( and PRODDATA to TESTDATA ) without changing the owner of the other tables. Is it possible to create a loop that will check the owner of all tables and change only those with the selected owner?
Thank you for your help.
January 6, 2003 at 3:47 pm
Sure. Following code should do it. The owner name seems to be case sensitive.
Dim oserver As SQLDMO.SQLServer
Dim odb As SQLDMO.Database
Dim otable As SQLDMO.Table
'create standard server object first
Set oserver = New SQLDMO.SQLServer
With oserver
.LoginSecure = True
.Connect "eg\one"
End With
'get db
Set odb = oserver.Databases("Northwind")
For Each otable In odb.Tables
If otable.Owner = "ANDY" Then
otable.Owner = "Steve"
End If
Next
Set odb = Nothing
oserver.DisConnect
Set oserver = Nothing
Andy
January 7, 2003 at 1:46 pm
Thanks, it did the job very well. Everything fine and up & running... 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply