·天新网首页·加入收藏·设为首页·网站导航
数码笔记本手机摄像机相机MP3MP4GPS
硬件台式机网络服务器主板CPU硬盘显卡
办公投影打印传真
家电电视影院空调
游戏网游单机动漫
汽车新车购车试驾
下载驱动源码
学院开发设计
考试公务员高考考研
业界互联网通信探索
SQL Server数据库中所有表及字段信息
http://www.21tx.com 2005年09月26日 lihonggen0

  程序思想:用SELECT name From sysobjects WHERE xtype = 'u'得到所有表,然后循环打开表,根据Rs_Colums.Fields(I).Name 得到字段名,FieldType(Rs_Colums.Fields(I).Type) 得到字段类型,Rs_Colums.Fields(I).DefinedSize '宽度

  由于Rs_Colums.Fields(I).Type返回类型是数字,程序中写了一个FieldType函数转化成中文类型

  Private Sub Command1_Click()

  Dim Cn As New ADODB.Connection

  Dim Rs_Table As New ADODB.Recordset

  Dim Rs_Colums As New ADODB.Recordset

  With Cn '定义连接

  .CursorLocation = adUseClient

  .Provider = "sqloledb"

  .Properties("Data Source").Value = "LIHG"

  .Properties("Initial Catalog").Value = "NorthWind"

  .Properties("User ID") = "sa"

  .Properties("Password") = "sa"

  .Properties("prompt") = adPromptNever

  .ConnectionTimeout = 15

  .Open

  If .State = adStateOpen Then

  Rs_Table.CursorLocation = adUseClient '得到所有表名

  Rs_Table.Open "SELECT name From sysobjects WHERE xtype = 'u'", Cn, adOpenDynamic, adLockReadOnly

  Rs_Table.MoveFirst

  Do While Not Rs_Table.EOF

  Debug.Print Rs_Table.Fields("name")

  Rs_Colums.CursorLocation = adUseClient

  Rs_Colums.Open "select top 1 * from [" & Rs_Table.Fields("name") & "]", Cn, adOpenStatic, adLockReadOnly

  For I = 0 To Rs_Colums.Fields.Count - 1 ' 循环所有列

  Debug.Print Rs_Colums.Fields(I).Name '字段名

  Debug.Print FieldType(Rs_Colums.Fields(I).Type) '字段类型

  Debug.Print Rs_Colums.Fields(I).DefinedSize '宽度

  Next

  Rs_Colums.Close

  Rs_Table.MoveNext

  Loop

  Rs_Table.Close

  Set Rs_Colums = Nothing

  Set Rs_Table = Nothing

  Else

  MsgBox "数据库连接失败,请找系统管理员进行检查 !", 16, cProgramName

  End

  End If

  End With

  End Sub

  '*********************************************************

  '* 名称:FieldType

  '* 功能:返回字段类型

  '* 用法:FieldType(nType as integer)

  '*********************************************************

  Function FieldType(nType As Integer) As String

  Select Case nType

  Case 128

  FieldType = "BINARY"

  Case 11

  FieldType = "BIT"

  Case 129

  FieldType = "CHAR"

  Case 135

  FieldType = "DATETIME"

  Case 131

  FieldType = "DECIMAL"

  Case 5

  FieldType = "FLOAT"

  Case 205

  FieldType = "IMAGE"

  Case 3

  FieldType = "INT"

  Case 6

  FieldType = "MONEY"

  Case 130

  FieldType = "NCHAR"

  Case 203

  FieldType = "NTEXT"

  Case 131

  FieldType = "NUMERIC"

  Case 202

  FieldType = "NVARCHAR"

  Case 4

  FieldType = "REAL"

  Case 135

  FieldType = "SMALLDATETIME"

  Case 2

  FieldType = "SMALLMONEY"

  Case 6

  FieldType = "TEXT"

  Case 201

  FieldType = "TIMESTAMP"

  Case 128

  FieldType = "TINYINT"

  Case 17

  FieldType = "UNIQUEIDENTIFIER"

  Case 72

  FieldType = "VARBINARY"

  Case 204

  FieldType = "VARCHAR"

  Case 200

  FieldType = ""

  End Select

  End Function

  此程序只是一个雏形,可以在此基础上开发成一个工具使用

  本程序在:VB 6.0 ,SQL SERVER 2000下运行通过

  注程序中须引用ActiveX Data Objects (ADO)


  

上一篇: 信息系统审计与信息系统工程监理的比较
下一篇: 多媒体知识全接触第四章 声音(音频)

关于我们 | 联系我们 | 加入我们 | 广告服务 | 投诉意见 | 网站导航
Copyright © 2000-2011 21tx.com, All Rights Reserved.
晨新科技 版权所有 Created by TXSite.net