程序思想:用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 Command
_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 =
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
* from [
& Rs_Table
Fields(
name
) &
]
Cn
adOpenStatic
adLockReadOnly
For I =
To Rs_Colums
Fields
Count
循環所有列
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
數據庫連接失敗
請找系統管理員進行檢查 !
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