OpenSchema(adSchemaTables)能列举出所有表和视图的字段名吗?
老问题,我想请问一下.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 name
from sysobjects
where xtype in ('U','V')

/*
name
--------------------------------------------------------
hy_fz_productTicketInfo
*/

select name
from sys.columns
where [object_id] = object_id('hy_fz_productTicketInfo')

/*
name
--------------------------------------------------------
SerialNo
TicketNo
MoCode
MoDId
PartId
SortSeq
cInvCode
cFree1
cFree2
cFree3
cFree4
cFree5
cFree6
cFree7
cFree8
cFree9
cFree10
MoRoutingDId
OperationId
OpSeq
WcId
WorkZone
StdManHour
BoxNo
TotalBoxNum
PersonCode
Quantity
oldQty
JarNo
bedNo
ScanDate
CreateDate
PTId
PTDId
bMakeOrder
WorkHrDId
DeptCode
GroupCode

(38 行受影响)
*/

------解决方案--------------------
那就按楼主需要的条件来晒徐其他列，例如有 a,b,c 三个列，需要的是a列重复，那么要筛选b,c列，聚合什么的,sum() max() min() 等。