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

2009-07-11 12:16:40 386 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

breezes2008 墨衍会员 · AI 创作全网分发关注 + 加入社区

VB窗体控件设计登录注册页面 VB窗体控件设计登录注册页面界面如下代码如下 Public Class Form1 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load End Sub Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click Dim myconnect As New SqlClient.Sq 阅读详情

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

...全文

告别Ctrl+Shift！用友U8自定义按钮开发保姆级教程（含VB代码示例）本文详细介绍了用友U8自定义按钮开发的完整流程，从环境配置到VB.NET代码实现，帮助用户告别传统的Ctrl+Shift操作方式。通过实战案例和代码示例，展示了如何在ERP系统中高效扩展自定义功能，提升业务处理效率。阅读详情

breezes2008

VB 16,718 成员 110,520 内容

+ 关注 + 加入社区

切换为时间正序

qshurufa 2009-07-12

打赏
举报

留个脚印！！

oec2003 2009-07-12

打赏
举报





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' )

书山有路通罗马 2009-07-12

打赏
举报

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

书山有路通罗马 2009-07-12

打赏
举报

刚好，我前两天为了学习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

jane_zhao 2009-07-11

打赏
举报

用这个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上。

a854468521 2009-07-11

打赏
举报

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

lsh2216024 2009-07-11

打赏
举报

路過

wuyq11 2009-07-11

打赏
举报

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

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

相关推荐