当前位置导航:炫浪网>>网络学院>>编程开发>>MsSQL教程

列出SQLSERVER数据库中所有表及字段信息


  程序思想:用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)
相关内容
赞助商链接