如何获取当前数据库的所有用户表名

[lo000ol]

请问在打开数据库后
Dim conn As ADODB.Connection
conn.Open connectstring
如何获取当前库的用户表名

[DaiLM]

sp_tables

[DaiLM]

SELECT *
FROM sysobjects
WHERE (xtype = 'U')

[youxia001]

不同类型的库有不同的方法

[hi128]

Dim strSQL As String
Dim conn As New ADODB.Connection
Dim rs, rs1 As New ADODB.Recordset
Dim str, selitem As String
Dim intloop, i As Integer

Private Sub Command1_Click()
Set conn = New ADODB.Connection
str = "Provider=Microsoft.Jet.OLEDB.4.0;Password='';User ID=Admin;Data Source=" & OpenDia.FileName & ";"
conn.Open str

Set rs = conn.OpenSchema(ADODB.SchemaEnum.adSchemaTables)
Do Until rs.EOF

If rs("table_Type").Value = "TABLE" Then
List1.AddItem (rs("table_Name").Value)
End If
rs.MoveNext
Loop

rs.Close
conn.Close

[noahman]

楼上的OpenSchema是一种方法，使用ADOX也是一种方法。注意他们得出的表名列表里面有一些是系统表，而非一般用户建的表。

[lo000ol]

如何排除这些系统表，而只提取一般用户建的表呢？

[suncyzz1314]

是SQL数据库可用下例代码：
Public Sub OpenSchemaX()

Dim cnn1 As ADODB.Connection
Dim rstSchema As ADODB.Recordset
Dim strCnn As String

Set cnn1 = New ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
cnn1.Open strCnn

Set rstSchema = cnn1.OpenSchema(adSchemaTables)

Do Until rstSchema.EOF
Debug.Print "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
rstSchema.MoveNext
Loop
rstSchema.Close

cnn1.Close

End Sub

[cf981231_sohu]

select * from sysobjects where type = 'U' order by name