Technical Article

Script Database Objects

,

Others have asked for this throught the disussion threads so I thought others might benefit as well.

This SQL-DMO script will create multiple files for server logins (SIDs & encrypted passwords), database users, tables, procedures and views.

1.  Alter the script to reflect your server and path for output files.
2.  Create the job (ActiveX Script).

Be great!
Michael

Dim oServer   ''As SQLDMO SQLServer
Dim oDatabase  ''As SQLDMO Database
Dim oTbl   ''As SQLDMO Table
Dim oProc   ''As SQLDMO Procedure
Dim oView   ''As SQLDMO View
Dim oLogin   ''As SQLDMO Logins
Dim oUsr ''As SQLDMO Users
Dim oTrg''AS SQLDMO Trigger

SET oServer = CreateObject("SQLDmo.SqlServer")

oServer.LoginSecure = True
oServer.Connect "<servername>"

    FOR EACH oLogin IN oServer.Logins
        ''SQLDMOScript_AppendToFile=256
        ''SQLDMOScript_ToFileOnly=64
        ''SQLDMOScript_PrimaryObject=4
''SQLDMOScript2_LoginSID=1048576
''SQLDMOScript2_EncryptPWD=128
''SQLDMOScript2_UnicodeFile=4
        oLogin.Script 256 + 64 + 4, "<path for output files>" & oServer.Name & "_LOGIN.sql",1048576 + 128 + 4
    NEXT

SET oDatabase = oServer.Databases("<databasename>")

   FOR EACH oUsr IN oDatabase.Users
        ''SQLDMOScript_AppendToFile=256
        ''SQLDMOScript_ToFileOnly=64
        ''SQLDMOScript_PrimaryObject=4
If oUsr.SystemObject = FALSE THEN
        oUsr.Script 256 + 64 + 4, "<path for output files>" & oServer.Name & "_" & oDatabase.Name & "_USER.sql"
END IF
    NEXT
    FOR EACH oTbl IN oDatabase.Tables
        ''SQLDMOScript_AppendToFile=256
''SQLDMOScript_Indexes=73736
''SQLDMOScript_OwnerQualify = 262144
        ''SQLDMOScript_ObjectPermissions=2
        ''SQLDMOScript_ToFileOnly=64
        ''SQLDMOScript_PrimaryObject=4
If oTbl.SystemObject = FALSE THEN
        oTbl.Script 256 + 73736 + 262144 + 2 + 64 + 4, "<path for output files>" & oServer.Name & "_" & oDatabase.Name & "_TBL.sql",,8388608
END IF
    NEXT
    FOR EACH oTbl IN oDatabase.tables
    FOR EACH oTrg IN oTbl.triggers
        ''SQLDMOScript_AppendToFile=256
        ''SQLDMOScript_ToFileOnly=64
        ''SQLDMOScript_PrimaryObject=4
If oTrg.SystemObject = FALSE THEN
        oTrg.Script 256 + 64 + 4, "<path for output files>" & oServer.Name & "_" & oDatabase.Name & "_TRIG.sql"
END IF
NEXT
NEXT
    FOR EACH oProc IN oDatabase.StoredProcedures
        ''SQLDMOScript_AppendToFile=256
        ''SQLDMOScript_ObjectPermissions=2
        ''SQLDMOScript_ToFileOnly=64
        ''SQLDMOScript_PrimaryObject=4
If oProc.SystemObject = FALSE THEN
        oProc.Script 256 + 2 + 64 + 4, "<path for output files>" & oServer.Name & "_" & oDatabase.Name & "_PROC.sql"
END IF
    NEXT
    FOR EACH oView IN oDatabase.views
        ''SQLDMOScript_AppendToFile=256
        ''SQLDMOScript_ObjectPermissions=2
        ''SQLDMOScript_ToFileOnly=64
        ''SQLDMOScript_PrimaryObject=4
If oView.SystemObject = FALSE THEN
        oView.Script 256 + 2 + 64 + 4, "<path for output files>" & oServer.Name & "_" & oDatabase.Name & "_VIEW.sql"
END IF
    NEXT

''clean up
oServer.DisConnect
SET oServer = Nothing

Rate

Share

Share

Rate