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.