通过系统表sysobjects、syscolumns等等(Sql Server) 给你一个例子: select syscolumns.name as col,systypes.name as type from syscolumns,systypes where syscolumns.usertype = systypes.usertype and id = object_id('Break_Mat') order by colid 分给我吧
http://www.csdn.net/cnshare/soft/10/10966.shtm
SELECT * From INFORMATION_SCHEMA.COLUMNS SELECT * From INFORMATION_SCHEMA.Tables
http://blog.csdn.net/online/archive/2004/09/08/98744.aspx 测试环境:WINXP+VB6添加2个列表框,1个按钮 '引用微软 ADO Ext.2.X for dll and SecurityDim cat As ADOX.CatalogDim cnn As ADODB.ConnectionDim tbl As ADOX.Table Private Sub Command1_Click()On Error Resume NextFor Each tbl In cat.Tables'如果是sqlserver数据库,则变成If Left(tbl.Name, 3) <> "sys"If Left(tbl.Name, 4) <> "MSys" ThenList1.AddItem tbl.NameEnd IfNextEnd Sub Private Sub Form_Load()Set cnn = New ADODB.ConnectionSet cat = New ADOX.Catalogcnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\csdn_vb\database\article.mdb"'cnn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=northwind;Data Source=yang"Set cat.ActiveConnection = cnnEnd Sub Private Sub Form_Unload(Cancel As Integer)Set cat = NothingSet con = NothingEnd Sub Private Sub List1_Click()Dim fldDim intfield As IntegerList2.Clearintfield = cat.Tables(List1.List(List1.ListIndex)).Columns.CountFor i = 0 To intfield - 1 Set fld = cat.Tables(List1.List(List1.ListIndex)).Columns(i) List2.AddItem fld.Name & " " & fld.Type & " " & fld.DefinedSizeNextEnd Sub
select * into dd from c where 1=2用这个命令可以复制一个空的表结构! 所以你只需要使用分布式查询,在其他数据库上建立就好了! 直接搞定!
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 = "127.0.0.1" .Properties("Initial Catalog").Value = "c3_hz" .Properties("User ID") = "sa" .Properties("Password") = "" .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
给你一个例子:
select syscolumns.name as col,systypes.name as type from syscolumns,systypes where syscolumns.usertype = systypes.usertype and id = object_id('Break_Mat') order by colid
分给我吧
SELECT * From INFORMATION_SCHEMA.Tables
测试环境:WINXP+VB6添加2个列表框,1个按钮 '引用微软 ADO Ext.2.X for dll and SecurityDim cat As ADOX.CatalogDim cnn As ADODB.ConnectionDim tbl As ADOX.Table Private Sub Command1_Click()On Error Resume NextFor Each tbl In cat.Tables'如果是sqlserver数据库,则变成If Left(tbl.Name, 3) <> "sys"If Left(tbl.Name, 4) <> "MSys" ThenList1.AddItem tbl.NameEnd IfNextEnd Sub Private Sub Form_Load()Set cnn = New ADODB.ConnectionSet cat = New ADOX.Catalogcnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\csdn_vb\database\article.mdb"'cnn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=northwind;Data Source=yang"Set cat.ActiveConnection = cnnEnd Sub Private Sub Form_Unload(Cancel As Integer)Set cat = NothingSet con = NothingEnd Sub Private Sub List1_Click()Dim fldDim intfield As IntegerList2.Clearintfield = cat.Tables(List1.List(List1.ListIndex)).Columns.CountFor i = 0 To intfield - 1 Set fld = cat.Tables(List1.List(List1.ListIndex)).Columns(i) List2.AddItem fld.Name & " " & fld.Type & " " & fld.DefinedSizeNextEnd Sub
select * into dd from c where 1=2用这个命令可以复制一个空的表结构!
所以你只需要使用分布式查询,在其他数据库上建立就好了!
直接搞定!
嘿嘿,简单把,偶就是这样做的,粉爽^_^
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 = "127.0.0.1"
.Properties("Initial Catalog").Value = "c3_hz"
.Properties("User ID") = "sa"
.Properties("Password") = ""
.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