急！！！vb.net怎样列出SQL数据库中所有表及表字段

[breezes2008]

已知数据库名字，例如pubs，列出pubs下所有的表名和相应表中所有的字段名，然后生成一颗树型，方便查看。
我是一个新手，在此先感谢大家！

[wuyq11]

SELECT b.name '表名',a.colorder '序号',a.name '字段名',c.name '类型',a.length '长度',a.isnullable '空值',
a.cdefault '默认值' FROM syscolumns a,sysobjects b,systypes c WHERE a.id=b.id and b.xtype='u'
and a.xtype=c.xtype order by b.name,a.colorder

取表名
SELECT distinct(b.name) '表名' FROM syscolumns a,sysobjects b,systypes c WHERE a.id=b.id and b.xtype='u'
and a.xtype=c.xtype order by b.name

[lsh2216024]

路過

[a854468521]

使用 System.Data.Common.DbConnection.GetSchema 类获得数据库的架构信息。

[jane_zhao]

用这个sql查询出所有表名和字段名：
select c.name as tableName ,a.name as columName from syscolumns a
inner join sysobjects c ON
a.id=c.id AND c.xtype='U' AND c.name<>'dtproperties'
where c.name in (select name from sysobjects where xtype='u' )
order by c.name
然后将这些数据循环添加到treeview上。

[书山有路通罗马]

刚好，我前两天为了学习vb自己写了个
Imports System.Collections.Generic
Imports System.Net
Imports System.Net.Sockets
Imports System.Net.Sockets.Socket
Imports System.Collections.Specialized
Imports System.Data.SqlClient

Public Class DBSqlServer
Private sqlCon As SqlConnection = Nothing

Public Function GetServers() As Dictionary(Of String, String)
Dim servers As New Dictionary(Of String, String)
'获取所有服务器
Try
Dim sqlApp As New SQLDMO.ApplicationClass '引用Interop.SQLDMO类
Dim sqlServers As SQLDMO.NameList '定义服务器列表
Dim SqlSrv As New SQLDMO.SQLServerClass '定义数据库列表
sqlServers = sqlApp.ListAvailableSQLServers '获得局域网内的可用SQL服务器列表
Dim i As Integer
For i = 0 To sqlServers.Count - 1
servers.Add(sqlServers.Item(i + 1), sqlServers.Item(i + 1))
Next
Catch ex As Exception
'MessageBox.Show(ex.Message)
End Try
Return servers
End Function

Public Function Getdbs(ByVal dbServer As String, ByVal dbUser As String, ByVal dbPass As String) As Dictionary(Of String, String)
Dim dbNames As New Dictionary(Of String, String)
'获取所有数据库
Try
Dim sqlApp As New SQLDMO.ApplicationClass '引用Interop.SQLDMO类
Dim sqlServers As SQLDMO.NameList '定义服务器列表
Dim SqlSrv As New SQLDMO.SQLServerClass '定义数据库列表

Dim SqlDB As New SQLDMO.Database '获得数据库列表
SqlSrv.Connect(dbServer, dbUser, dbPass)
For Each SqlDB In SqlSrv.Databases
dbNames.Add(SqlDB.Name, SqlDB.Name)
Next
Catch ex As Exception
'MessageBox.Show(ex.Message)
End Try
Return dbNames
End Function

Public Function GetTables(ByVal dbSever As String, ByVal dbName As String, ByVal dbUser As String, ByVal dbPass As String) As Dictionary(Of String, dbTable)
'获取所有表
Dim tables As New Dictionary(Of String, dbTable)
Dim strConnection = "data source=" & dbSever & ";initial catalog=" & dbName & ";user id=" & dbUser & ";pwd=" & dbPass
Try
If sqlCon Is Nothing Then
Try
sqlCon = New SqlConnection(strConnection)
sqlCon.Open()
Catch e As Exception
MsgBox(e.Message)
End Try
End If

If sqlCon.State = ConnectionState.Open Then

Dim tbSql As String = "SELECT Name FROM " & dbName & "..SysObjects Where XType='U' ORDER BY Name"
Dim tbDS As DataSet = New DataSet
Dim tbDA As SqlDataAdapter = New SqlDataAdapter(tbSql, sqlCon)
tbDA.Fill(tbDS)
For Each r As DataRow In tbDS.Tables(0).Rows
Dim tb As New dbTable
tb.Name = r(0)
Dim clSql As String = "SELECT [Name],[xtype],[length] FROM SysColumns WHERE id=Object_Id('" & r(0) & "')"
Dim clDS As DataSet = New DataSet
Dim clDA As SqlDataAdapter = New SqlDataAdapter(clSql, sqlCon)
clDA.Fill(clDS)
For Each r1 As DataRow In clDS.Tables(0).Rows
Dim cln As New Column
cln.Name = r1("Name")
cln.Type = r1("xtype")
cln.Length = r1(2)
tb.Columns.Add(cln.Name, cln)
Next
tables.Add(tb.Name, tb)
Next

End If
Catch ex As Exception
' MessageBox.Show(ex.Message)
End Try
Return tables
End Function

[书山有路通罗马]

以上可以 列出 数据库
选择库 列出表
选择表 列出列 Dim clSql As String = "SELECT [Name],[xtype],[length] FROM SysColumns 。。。。。。。这 下 面是获取列

[oec2003]

select 
	sys.tables.name as TableName,--表名
    sys.columns.name as ColumnName  --字段名
from 
    sys.columns, sys.tables
where 
    sys.columns.object_id = sys.tables.object_id 
and 
    sys.tables.name in (select name from sysobjects where xtype='u' )

[qshurufa]

留个脚印！！