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

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

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