sp;
csdn许多网友问怎样列出数据库有所有表及表信息,下面这段程序正是你所想要的,
程序思想:用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 \"selecttop 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\"
如果本文没有解决您的问题,请进老妖怪开发者社区提问