Retrieval of SQL database system Object details

How to get the names of Tables, Views, Stored Procedures and User Defined Functions of SQL Server database Using SQLDMO Class?

The following code VB.NET code snippet explains how to retrieve the names of tables, views, stored procedures and user defined functions of SQL Server database using SQLDMO class.

Private Sub cmdDisplayObjectDetails_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDisplayObjectDetails.Click

'Make the Database connection

Dim strSQL As String = ""

strSQL = "Provider=SQLOLEDB.1;data source=(local);user id='test';Password='test';database='TEST'"

Dim Conn As New OleDbConnection(strSQL)

Try

Conn.Open()

Conn.Close()

Catch ex As Exception

MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop)

Exit Sub

End Try

'Display the System Object Details

RetrieveAllObjects()

End Sub

Public Sub RetrieveAllObjects()

'Declaration of database

Public MYDatabase As SQLDMO.Database2

Dim MyDBServer As New SQLDMO.SQLServer2

'Connect to the Database –Server Name, User Name and Password

MyDBServer.Connect("(local)", "test", "test")

'Populate all the system object details

MyDatabase = Nothing

Try

'Database Name: TEST

MyDatabase = MyDBServer.Databases.Item("TEST")

MyDatabase.DBOption.SelectIntoBulkCopy = True

Catch ex As Exception

MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop)

Exit Sub

End Try

'Get Table Details

Dim i As Integer

For i = 1 To MyDatabase.Tables.Count

If Not MyDatabase.Tables.Item(i).SystemObject Then

Console.Writeln(MyDatabase.Tables.Item(i).Name )

End If

Next

'Get View Details

For i = 1 To MyDatabase.Views.Count

If Not MyDatabase.Tables.Item(i).SystemObject Then

Console.Writeln(MyDatabase.Views.Item(i).Name )

End If

Next

'Get Stored procedure Details

For i = 1 To MyDatabase.StoredProcedures.Count

If Not MyDatabase.StoredProcedures.Item(i).SystemObject Then

Console.Writeln(MyDatabase.StoredProcedures.Item(i).Name )

End If

Next

'Get User Defined Function Details

For i = 1 To MyDatabase.UserDefinedFunctions.Count

If Not MyDatabase.UserDefinedFunctions.Item(i).SystemObject Then

Console.Writeln(MyDatabase.UserDefinedFunctions.Item(i).Name )

End If

Next

End Function

Code Explanation:

The connection to the SQL Server is made using OLEDB provider. After establishing the connection, the SQLDMO SQLServer2 and SQLDMO Database2 classes are used to retrieve the details of database.