熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

列出SQLSERVER數據庫中所有表及字段信息

2013-11-15 14:35:56  來源: SQL Server 

  程序思想:用SELECT name From sysobjects WHERE xtype = u得到所有表然後循環打開表根據Rs_ColumsFields(I)Name  得到字段名FieldType(Rs_ColumsFields(I)Type) 得到字段類型Rs_ColumsFields(I)DefinedSize 寬度
  
  由於Rs_ColumsFields(I)Type返回類型是數字程序中寫了一個FieldType函數轉化成中文類型
  
  Private Sub Command_Click()
  Dim Cn As New ADODBConnection
  Dim Rs_Table As New ADODBRecordset
  Dim Rs_Colums As New ADODBRecordset
  
  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 =
  Open
  
  If State = adStateOpen Then
  Rs_TableCursorLocation = adUseClient  得到所有表名
  Rs_TableOpen SELECT name From sysobjects WHERE xtype = u Cn adOpenDynamic adLockReadOnly
  Rs_TableMoveFirst
  Do While Not Rs_TableEOF
  DebugPrint Rs_TableFields(name)
  Rs_ColumsCursorLocation = adUseClient
  Rs_ColumsOpen select top * from [ & Rs_TableFields(name) & ] Cn adOpenStatic adLockReadOnly
  For I = To Rs_ColumsFieldsCount   循環所有列
  DebugPrint Rs_ColumsFields(I)Name  字段名
  DebugPrint FieldType(Rs_ColumsFields(I)Type) 字段類型
  DebugPrint Rs_ColumsFields(I)DefinedSize 寬度
  Next
  Rs_ColumsClose
  Rs_TableMoveNext
  Loop
  Rs_TableClose
  Set Rs_Colums = Nothing
  Set Rs_Table = Nothing
  
  Else
  MsgBox 數據庫連接失敗請找系統管理員進行檢查 ! cProgramName
  End
  End If
  End With
  End Sub
  
  *********************************************************
  * 名稱FieldType
  * 功能返回字段類型
  * 用法FieldType(nType as integer)
  *********************************************************
  Function FieldType(nType As Integer) As String
  Select Case nType
  Case
  FieldType = BINARY
  Case
  FieldType = BIT
  Case
  FieldType = CHAR
  Case
  FieldType = DATETIME
  Case
  FieldType = DECIMAL
  Case
  FieldType = FLOAT
  Case
  FieldType = IMAGE
  Case
  FieldType = INT
  Case
  FieldType = MONEY
  Case
  FieldType = NCHAR
  Case
  FieldType = NTEXT
  Case
  FieldType = NUMERIC
  Case
  FieldType = NVARCHAR
  Case
  FieldType = REAL
  Case
  FieldType = SMALLDATETIME
  Case
  FieldType = SMALLMONEY
  Case
  FieldType = TEXT
  Case
  FieldType = TIMESTAMP
  Case
  FieldType = TINYINT
  Case
  FieldType = UNIQUEIDENTIFIER
  Case
  FieldType = VARBINARY
  Case
  FieldType = VARCHAR
  Case
  FieldType =
  End Select
  End Function
  
  此程序只是一個雛形可以在此基礎上開發成一個工具使用
  
  本程序在VB  SQL SERVER 下運行通過
  
  注程序中須引用ActiveX Data Objects (ADO)

From:http://tw.wingwit.com/Article/program/SQLServer/201311/22042.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.