vb 列出SQL SERVER数据库中所有表及字段信息

vb 列出SQL SERVER数据库中所有表及字段信息【转】  

2010-05-06 09:28:24|  分类： 默认分类 |  标签： |字号大中小 订阅

vb 列出SQL SERVER数据库中所有表及字段信息

2009-06-02 10:21

许多网友问怎样列出数据库有所有表及表信息，下面这段程序正是你所想要的, 程序思想:用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)