How can I get all tables' names from MS SQL database or Access Databas

[ii128]

How can I get all tables' names from MS SQL database or Access Databas?

 

[gibsonmd]

This should show you all the user tables in Sql Server database:

select * from sysobjects where xtype = 'u'

 

[RickCole]

In SQL Server 7.0 (Sorry not sure about access)

select * from sysobjects where type = 'u'

Will give you all user tables held within the database.

Other items held within sysobjects where type = the following are as follows:

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
K = PRIMARY KEY or UNIQUE constraint
L = Log
P = Stored procedure
R = Rule
RF = Replication filter stored procedure
S = System table
TR = Trigger
U = User table
V = View
X = Extended stored procedure


Hope this helps

Rick Cole.

 

[ii128]

Does anyone know how to get all table's names from MS Access?

 

[AccessSQLUser]

Try this:

Public Sub GetTableNames()

Dim x As Integer

For x = 0 To CurrentDb.TableDefs.Count - 1
Debug.Print CurrentDb.TableDefs(x).Name
Next x

End Sub

 

[tlbroadbent]

Here is a query that lists user tables, including linked tables.

SELECT MSysObjects.Name, MSysObjects.Type, MSysObjects.Flags
FROM MSysObjects
WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0)) OR (((MSysObjects.Type)=6));
Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[ii128]

tlbroadbent,

I have a Error message on MS Access.

"Reacord(s) cannot be read; no read permission on 'MySysObject'."

 

[tlbroadbent]

Is your logon a member of the Admins group? Which version of Access? Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous