(c) 2002 Visual Studio Magazine 
Fawcette Technical Publications

Issue: March 2002
Section: Database Design
Author: Michael Domingo

VB6, ADO 2.5	OpenSchema Sheds Light on Database Objects
Listing 1	ADO returns a wealth of schema information. In addition to the TABLE_TYPE and TABLE_SCHEMA fields, uses the TABLE_NAME field to filter out system tables. Here, you filter out tables names that begin with "sys", "dt", and "usp".

Set oRS = oADOConn.OpenSchema(adSchemaTables)
While Not oRS.EOF
	' Ignore system tables and schema tables
	If oRS!TABLE_TYPE <> "SYSTEM TABLE" And _
		oRS!TABLE_SCHEMA <> "INFORMATION_SCHEMA" _
		Then
		If oRS!TABLE_TYPE = "TABLE" Or _
			oRS!TABLE_TYPE = "VIEW" Then
			' Ignore system tables/views
			If Left$(oRS!TABLE_NAME, 3) <> "sys" _
				And Left$(oRS!TABLE_NAME, 2) <> _
				"dt" And Left$(oRS!TABLE_NAME, 2) _
				<> "usp" Then
				' Add this object to your object 
				' collection
				Set oObject = New DBObject
				With oObject
					.Name = Trim$("" & _
						oRS!TABLE_NAME)
					Select Case oRS!TABLE_TYPE
						Case "TABLE": .DBType = _
							adSchemaTables
						Case "VIEW": .DBType = _
							adSchemaViews
					End Select
					.Found = False
				End With
				mObjects.Add oObject
			End If
		End If
	End If
	oRS.MoveNext
Wend
oRS.Close

VB6, SQL-DMO	Sniff Out Database Dependencies
Listing 2	The SQL-DMO EnumDependencies method is the ticket to figuring out whether a database object has dependencies. Note how you must call the EnumDependencies method separately for each object type.

Public Sub GetDependencies(ByVal sServer As _
	String, ByVal sDatabase As String)
Dim oSQLServer As SQLDMO.SQLServer
Dim oDB As SQLDMO.Database
Dim oDBObject As DBObject
Dim oDependencies As SQLDMO.QueryResults
Set oSQLServer = New SQLDMO.SQLServer
With oSQLServer
	.LoginTimeout = 10
	.Connect sServer, "sa", ""
End With
Set oDB = oSQLServer.Databases(sDatabase)
For Each oDBObject In mObjects
	If oDBObject.SearchFor Then
		sName = oDBObject.Name
		Select Case oDBObject.DBType
			Case adSchemaTables: Set _
				oDependencies = _
				oDB.Tables(sName).EnumDependencies _
				(SQLDMODep_Children)
			Case adSchemaProcedures: Set _
				oDependencies =
				oDB.StoredProcedures(sName).
				EnumDependencies(SQLDMODep_Children)
			Case adSchemaViews: Set oDependencies _
				= oDB.Views(sName) _
				.EnumDependencies _
				(SQLDMODep_Children)
		End Select
		If oDependencies.Rows > 0 Then
			oDBObject.HasDependencies = True
		End If
		Set oDependencies = Nothing
	End If
Next oDBObject
End Sub
