Solved How to get a list of all tables in a sql server database

[KitaYama]

Looping through TableDefs gives us a list of all tables in a BE.
Is there any way to create a list of all user type tables (non-system) in a sql server database?

Thank you.

 

[Josef P.]

SQL-Server sys view:

select * from sys.tables where type = 'U'

 

[KitaYama]

You may have not noticed. The question is in VBA sub forum.
I need it from vba in a FE.

Thanks for trying to help.

 

[Josef P.]

And you can't open a recordset in VBA with this SQL statement?

As an alternative, you could look at OpenSchema from ADODB.
TableDefs could also be run through, but I don't know how to get only the user tables. Unless it is enough to exclude the schema sys.

 

[KitaYama]

And you can't open a recordset in VBA with this SQL statement

Sorry later I noticed what you meant by that.
I was in a hurry and couldn't correct my reply.

I'll try your suggestion and will be back if I faced a trouble I couldn't solve.

Thanks again.

 

[GPGeorge]

You may have not noticed. The question is in VBA sub forum.
I need it from vba in a FE.

Thanks for trying to help.

You could write that SQL statement in a passthru and execute it to retrieve the tables from sys.tables

It may take additional filtering to limit to what you need or want.

 

[isladogs]

If you just want the SQL Server tables that have been linked to the FE, you could also run a query on MSysObjects filtering for WHERE Type =4

 

[KitaYama]

If you just want the SQL Server tables that have been linked to the FE, you could also run a query on MSysObjects filtering for WHERE Type =4

@isladogs thanks for your help.
But no, I'm trying to link sql server tables to my database. Not refreshing the current linked tables. If after an update a table is added to sql server,
checking MSysObjects table, is not enough.

Thank you.

 

[KitaYama]

@Josef P. This morning I had a little free time to test.
Your solution works. But I see two records in "select * from sys.tables where type = 'U'" that are not user tables.
dtProperties & sysdiagrams

For now I filtered them out.

Thanks for your help.