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

王朝mssql·作者佚名  2008-05-19
窄屏简体版  字體: |||超大  

程序思想:用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)

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航