Alex Alex - 5 days ago 7
SQL Question

How to Retrieve All Tables From SQL Database (Excluding System Tables)

Goal



To retrieve all tables from SQL Database while excluding the system tables.

Table retrieval

The tables in green notify the ones I want and the red rectangle are the ones I want to exclude (system tables)




Attempt



Here's what I tried

Private Function GetTables(ByVal cnSql As String) As List(Of String)
Dim lst_Tables As New List(Of String)
Dim mycn As SqlConnection
Dim myCmd As SqlDataAdapter
Dim myData As New DataSet()

mycn = New SqlConnection(cnSql) 'Properly connects to my database
mycn.Open()
myCmd = New SqlDataAdapter("SELECT * FROM INFORMATION_SCHEMA.tables ORDER BY TABLE_NAME", mycn)
myCmd.Fill(myData)
mycn.Close()

For Each table As DataTable In myData.Tables
For Each row As DataRow In table.Rows
'Only fetch tables (ommit views)
If row.ItemArray(3).ToString = "BASE TABLE" Then
For Each col As DataColumn In table.Columns
If col.ToString() = "TABLE_NAME" Then
lst_Tables.Add(row(col).ToString())
Exit For
End If
Next
End If
Next
Next

Return lst_Tables
End Function


Unfortunately, this also returns the System Tables ... How would I go about not including these?

Answer

Use this query instead of yours:

SELECT * FROM sys.objects WHERE type = 'U'

or

SELECT * FROM sys.tables

You can also use the is_ms_shipped = 0 in your predicate.

Comments