SQLDMO testing

1 SQLDMO(testing)
2 ‘Connecting using SQL Authentication
3 Dim objDMO As SQLServer
4 objDMO = New SQLDMO.SQLServer
5 objDMO.Connect("(local)", "sa", "of_course_I_changed_my_password")
6 Dim objSQLServer As SQLDMO.SQLServer
7 objSQLServer = New SQLDMO.SQLServer
8 objSQLServer.LoginSecure = True
9 Call objSQLServer.Connect("cnmtldv01psge")
10
11 Console.WriteLine(objSQLServer.Name())
12 Console.WriteLine(objSQLServer.HostName())
13 Console.WriteLine(objSQLServer.Status())
14
15 display SpaceAvailableInMB for each Database
16 Chr(9): tab; chr(13): carriage return; chr(10): line feed
17 Dim oDatabase As SQLDMO.Database
18 For Each oDatabase In objSQLServer.Databases
19 Console.WriteLine(oDatabase.Name & Chr(9) & oDatabase.SpaceAvailableInMB & Chr(13))
20 Next
21
22 ‘create a database
23 Dim nDatabase As New SQLDMO.Database
24 Dim nDBFileData As New SQLDMO.DBFile
25 Dim nLogFile As New SQLDMO.LogFile
26 Dim databaseName As String
27 databaseName = "zzz"
28 nDatabase.Name = databaseName
29 nDBFileData.Name = databaseName
30 nDBFileData.PhysicalName = "D:\\sqldata\\MSSQL\\Data\\" & databaseName & "_Data.mdf"
31 nDBFileData.PrimaryFile = True
32 nDBFileData.Size = 2
33 nDBFileData.MaximumSize = 10
34 nDBFileData.FileGrowthType = SQLDMO.SQLDMO_GROWTH_TYPE.SQLDMOGrowth_Percent
35 nDBFileData.FileGrowth = 10
36 nDatabase.FileGroups.Item("PRIMARY").DBFiles.Add(nDBFileData)
37 nLogFile.Name = databaseName + "Log"
38 nLogFile.PhysicalName = "D:\\sqldata\\MSSQL\\Data\\" & databaseName + "_Log.ldf"
39 nLogFile.Size = 2
40 nLogFile.MaximumSize = 5
41 nDatabase.TransactionLog.LogFiles.Add(nLogFile)
42 objSQLServer.Databases.Add(nDatabase)
43
44 Update(AutoUpdateStat)
45 Dim objDBz As SQLDMO._Database
46 objDBz = objSQLServer.Databases.Item("zxx")
47 objdbz.Owner as string
48 objDBz.CreateDate as string
49 objdbz.Size readonly as integer
50 objdbz.SpaceAvailableInMB as single
51 If objDBz.DBOption.AutoUpdateStat = False Then
52 objDBz.DBOption.AutoUpdateStat = True
53 End If
54
55 ‘query the database
56 Dim oTheResults As SQLDMO.QueryResults
57 oTheResults = objDBz.ExecuteWithResults("SELECT * FROM Table1") ‘Length of the Batch. For example, we are currently using 50 characters.
58 Dim numOfCols As Integer
59 numOfCols = oTheResults.Columns
60 Dim num As Integer
61 For num = 1 To oTheResults.Rows
62 Dim j As Integer
63 For j = 1 To numOfCols
64 Console.Write(oTheResults.GetColumnString(num, j))
65 Console.Write(Chr(9))
66 Next
67 Console.Write(Chr(10))
68 Next
69
70 ‘display tables inside a database; Assuming, we have set up our connection.
71 Dim objDB As SQLDMO.Database
72 objDB = objSQLServer.Databases.Item("northwind")
73 Dim oTable As SQLDMO.Table
74 For Each oTable In objDB.Tables
75 ‘Console.WriteLine(oTable.Name)
76 Next
77
78
79 ‘scripting: Assuming, we have set up our connection.
80 Dim oTable2 As SQLDMO.Table
81 oTable2 = objDB.Tables.Item("employees")
82 ‘Console.WriteLine("Script of table employees: " & oTable2.Script())
83
84 ‘display indexed columns
85 Dim indexcolname As String
86 Dim idxcnt As Integer
87 Dim idxloops As Integer
88 Dim colCount As Integer
89 Dim idxObj As SQLDMO.Index
90 Dim idxcol As SQLDMO.Column
91 idxcnt = 0
92 objDB = objSQLServer.Databases.Item("zxx")
93 Dim objTable As SQLDMO.Table
94 objTable = objDB.Tables.Item("Table1")
95 idxcnt = objTable.Indexes.Count ‘ number of indexes in the table
96 Dim i As Integer
97 For i = 1 To idxcnt
98 idxObj = objTable.Indexes.ItemByID(i) ‘one-based (instead of zero based)
99 colCount = idxObj.ListIndexedColumns.Count ‘number of cols in this index
100 Dim j As Integer
101 For j = 1 To colCount
102 idxcol = idxObj.ListIndexedColumns.Item(j)
103 ‘Console.WriteLine("index Name: " & idxObj.Name & " __and col Name of it: " & idxcol.Name)
104 Next
105 Next
106
107 ‘display and update config values
108 Dim cv As SQLDMO.ConfigValue
109 With objSQLServer.Configuration
110 .ShowAdvancedOptions = True
111 For Each cv In .ConfigValues
112 ‘Console.WriteLine(cv.Name & "_" & cv.CurrentValue & "_" & cv.RunningValue)
113 Next
114 End With
115
116 following is not tested
117 allow changes to system tables
118 With SQLServer.Configuration
119 .ConfigValues("allow updates").CurrentValue = 1
120 .ReconfigureWithOverride()
121 End With
122
123 display table information
124 Dim col2 As SQLDMO.Column
125 col2 = objTable.Columns.ItemByID(1)
126 Dim ie As IEnumerator = col2.Datatype.GetEnumerator()
127 While ie.MoveNext
128 Console.WriteLine(ie.Current.ToString())
129 End While
130
131 objSQLServer.DisConnect()
132
133 Console.WriteLine(goSQLApp.SQLServers.Item("(local)").Databases.Item("zxx").Tables.ItemByID(0).Name())