Automating SQLServer from VB-Part-I

Automating SQLServer from VB (using Microsoft SQLDMO Obect Lirary) - Part-I

The article looks at automating Microsoft SQL server from visual basic using SQL Distributed Management Objects (SQLDMO object library). In this part, I'll explain about connecting to Sqlserver, getting a list of all the available databases, tables and fields.



The first thing you need to do before getting started is to add a reference to Microsoft SQLDMO Obect Lirary from VB. (goto Project-->References-->Microsoft SQLDMO Object Library)

	'Declaring Object Variables
	Dim oSQLServer As New SQLDMO.SQLServer
	Dim oDatabase As SQLDMO.Database
	Dim oTable As SQLDMO.Table
	Dim oColumn As SQLDMO.Column

Now, having declared all the required variables, it's about time we connected to the server.
And this is how we do it.

	'EITHER connect using server name, username and password (sql server authentication)
	oSQLServer.Connect "IC6", "forApp", "mahipal"
	'connect  using a direct secure connection (Windows authentication)
	oSQLServer.LoginSecure = True
	oSQLServer.Connect "(local)" 'replace with your server name if not a local installation

Now that we've connected to the database, let's get the list of all the databases in the server

	'get a list of databases from a SQLServer
	For Each oDatabase In oSQLServer.Databases
		If oDatabase.Status <> SQLDMODBStat_Inaccessible Then   'if it's accessible, go ahead
			List1.AddItem oDatabase.Name
		End If

And this is how we get the Table list form a given database

	'get a list of tables from a database	
	For Each oTable In oSQLServer.Databases("Northwind").Tables
		List1.AddItem oTable.Name

Lastly, this is how we get the Fields in a given table, their datatypes, length and if they can take NULLs or not.

	'get list of columns, their datatypes, size, null or not null etc.  from a table
	Set oTable = oSQLServer.Databases("Northwind").Tables("Customers")
	For Each oColumn In oTable.Columns
		If oColumn.AllowNulls Then strTemp = "NULL" Else strTemp = "NOT NULL"
		List1.AddItem oColumn.Name & "  -  " & oColumn.Datatype & "  (" & oColumn.Length & ")  -  " & strTemp
	Next oColumn

This concludes Part-I of the article on SQLDMO. In Part-II , I'll look at creating a database, adding tables to the database, and adding columns to tables using SQLSMO.

Terms and Conditions of Use
Copyright 2004 - 2006 Mahipal Padigela. All rights reserved.