Article Options
Premium Sponsor
Premium Sponsor

 »  Home  »  Data Programming  »  SQL Server  »  An introduction to SQL Server Management Objects  »  Enumerating database tables, views and stored procedures
An introduction to SQL Server Management Objects
by David Jeavons | Published  01/02/2007 | SQL Server | Rating:
Enumerating database tables, views and stored procedures

To enumerate all tables within a database, you need to enumerate the Tables collection of a Database object. The following example will add all table names to a list box:

    1         Dim db As Database = sqlServer.Databases("MyNewDatabase")

    2 

    3         For Each tb As Table In db.Tables

    4             tablesListBox.Items.Add(tb.Name)

    5         Next

 

You can also retrieve the columns of a table by enumerating the table’s Columns collection:

 

    1         Dim db As Database = sqlServer.Databases("MyNewDatabase")

    2         Dim tb As Table = db.Tables("TableName")

    3 

    4         For Each col As Column In tb.Columns

    5             columnsListBox.Items.Add(col.Name)

    6         Next

 

The code for enumerating Views and Stored Procedures is almost identical to the tables enumeration code above; the only difference is the collection that is enumerated.

 

Views:

 

    1         Dim db As Database = sqlServer.Databases("MyNewDatabase")

    2 

    3         For Each view As View In db.Views

    4             viewsListBox.Items.Add(view.Name)

    5         Next

 

Stored Procedures:

 

    1         Dim db As Database = sqlServer.Databases("MyNewDatabase")

    2 

    3         For Each sp As StoredProcedure In db.StoredProcedures

    4             storedProcedureListBox.Items.Add(sp.Name)

    5         Next

 

One point worth noting is that the enumerations above will include system objects. If you do not wish to view these system objects then you can query the IsSystemObject property of the table, view or stored procedure.

Sponsored Links