老问题,我想请问一下.OpenSchema(adSchemaTables)能列举出所有表和视图的各个字段名字段属性吗?
下面我的代码是经过测试是正确的,但唯一的缺点:只有表名和表的所有者,
Sub dd()
'经过测试这是一个正确的调用所有数据库表名和视图名的例子代码
Dim cnn1 As ADODB.Connection
Dim rstSchema As ADODB.Recordset
Dim strCnn As String
Set cnn1 = New ADODB.Connection
'strCnn = "driver={SQL Server};server=192.168.32.8; uid=kerwin;pwd=yiyao;database=seasons "
strCnn = "Provider=sqloledb;server=192.168.32.3;Database=SEASONS;Uid=kerwin;Pwd=yiyao;"
cnn1.Open strCnn
Set rstSchema = cnn1.OpenSchema(adSchemaTables)
i = 1
Do Until rstSchema.EOF
Sheets(1).Cells(i, 1) = rstSchema!TABLE_NAME
Sheets(1).Cells(i, 2) = rstSchema!TABLE_type
Sheets(1).Cells(i, 3) = rstSchema!TABLE_CATALOG
Sheets(1).Cells(i, 4) = rstSchema!TABLE_SCHEMA
''Sheets(1).Cells(i, 5) = rstSchema!COLUMN_NAME 这句本来是在ACCESS中的用的,但报错要求访求对象
i = i + 1
rstSchema.MoveNext
Loop
rstSchema.Close
cnn1.Close
End Sub
希望高手能出手解决一下,在Sheets(1).Cells(i,N) = 增加各个表(视图)的字段名.....我搞了好久无解..谢谢!
而我用 select * from sysobjects ORDER BY name 却只有表而没有视图的字段名 或者:
有没有查询所有数据库表和视图字段名及类型 的select 语句
------解决方案--------------------
- SQL code
select namefrom sysobjectswhere xtype in ('U','V')/*name--------------------------------------------------------hy_fz_productTicketInfo*/select namefrom sys.columnswhere [object_id] = object_id('hy_fz_productTicketInfo')/*name--------------------------------------------------------SerialNoTicketNoMoCodeMoDIdPartIdSortSeqcInvCodecFree1cFree2cFree3cFree4cFree5cFree6cFree7cFree8cFree9cFree10MoRoutingDIdOperationIdOpSeqWcIdWorkZoneStdManHourBoxNoTotalBoxNumPersonCodeQuantityoldQtyJarNobedNoScanDateCreateDatePTIdPTDIdbMakeOrderWorkHrDIdDeptCodeGroupCode(38 行受影响)*/
------解决方案--------------------
那就按楼主需要的条件来晒徐其他列，例如有 a,b,c 三个列，需要的是a列重复，那么要筛选b,c列，聚合什么的,sum() max() min() 等。