Unable to retrieve table names in a database

Transact-SQL
1

When I executed the query below in SSMS, the table names in 'MyDataBase' database were returned correctly. However, when I ran the same SQL command in a VB application using a SqlDataReader, no error but no table names were returned either. Any idea? Thanks!

SELECT [TABLE_NAME] FROM [MyDataBase].[INFORMATION_SCHEMA].[TABLES];

VB code:

Dim cmdTxt As String = "SELECT [TABLE_NAME] FROM [MyDataBase].[INFORMATION_SCHEMA].[TABLES];"
        Using conn As New SqlConnection(conString)
            Dim cmd As New SqlCommand(cmdTxt, conn)
            Dim reader As SqlDataReader
            Try
                conn.Open()
                reader = cmd.ExecuteReader
                While reader.Read()
                    Dim name = reader.GetString(0)
                    Me.cboTableNames.Items.Add(name)
                End While
                reader.Close()
            Catch ex As Exception
                Module1.WriteErrorLog("FormViewData.GetTableNameList(): ", ex.Message)
                frmMain.StatusUpdate("Error occured while obtaining list of table names. Reason: " & ex.Message, True)
            End Try
        End Using
2

Are you using the same user/login/credential in the app that you're logging in with in SSMS? If the app is using a different credential, it may not have the permissions to view the DB schema.

You may need to grant VIEW DEFINITION permission to the login the app uses.

1 Like
3

The same credentials were used in SSMS and my VB application.

4

Try adding SET NOCOUNT ON before the actual call.

I would also recommend not using the information schema tables - and instead using 'sys.tables' to get the list of tables from SQL Server.