Oracle&Excel VBA写获取表字段类型及约束语句

Oracle：

   select 
  /*字段及注释*/t1.owner,t1.table_name,t2.comments,t1.column_name,t3.comments,
  /*字段类型*/t1.data_type,t1.DATA_LENGTH,t1.DATA_PRECISION, 
  /*字段约束*/t1.nullable,t6.c_unique,t6.c_primary,t6.c_rela
  from all_tab_columns t1 
  left join all_tab_comments t2 on t1.owner=t2.owner and t1.table_name=t2.table_name 
  left join all_col_comments t3 on t1.owner=t3.owner and t1.table_name=t3.table_name and t1.column_name=t3.column_name
  left join 
  (select * from 
   (select t4.OWNER,t4.TABLE_NAME,t4.COLUMN_NAME,t5.CONSTRAINT_TYPE from all_cons_columns t4 
      left join all_constraints t5 on t4.constraint_name=t5.constraint_name and t4.owner=t5.owner and t4.table_name=t5.table_name
      where t4.TABLE_NAME= v_table_name and CONSTRAINT_TYPE<>'C' --******传表名
    ) 
   pivot (max(CONSTRAINT_TYPE) for CONSTRAINT_TYPE in ('U' as c_unique,'P' as c_primary,'R' as c_rela)) 
   ) t6
   on t1.owner=t6.owner and t6.TABLE_NAME=t1.TABLE_NAME and t1.column_name=t6.column_name 
   where t1.OWNER= v_owner and t1.TABLE_NAME=v_table_name ORDER BY T1.COLUMN_ID ;  --******传表名 

Excel VBA

Dim conn As New ADODB.Connection 
Dim strConn As String
Dim strconn1 As String
Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.recordset") 
sqls = ""
sqls = sqls & "select "
sqls = sqls & "t1.owner as ""用户名"",t1.table_name as ""表名称"",t2.comments as ""表注释"",t1.column_name as ""字段名称"",t3.comments as ""字段注释"", "
sqls = sqls & "t1.data_type as ""字段类型"",t1.data_length as ""字段长度"" ,t1.data_precision as ""字段精度"", "
sqls = sqls & "t1.nullable as ""是否允许为空"",t6.c_primary as ""是否为空"" , t6.c_unique as ""是否唯一"",t6.c_rela as ""是否关联外键"" "
sqls = sqls & "from all_tab_columns t1 "
sqls = sqls & "left join all_tab_comments t2 on t1.owner=t2.owner and t1.table_name=t2.table_name "
sqls = sqls & "left join all_col_comments t3 on t1.owner=t3.owner and t1.table_name=t3.table_name and t1.column_name=t3.column_name "
sqls = sqls & "Left Join "
sqls = sqls & "(select * from "
sqls = sqls & "(select t4.OWNER,t4.TABLE_NAME,t4.COLUMN_NAME,t5.CONSTRAINT_TYPE from all_cons_columns t4 "
sqls = sqls & "left join all_constraints t5 on t4.constraint_name=t5.constraint_name and t4.owner=t5.owner and t4.table_name=t5.table_name "
sqls = sqls & "  where t4.TABLE_NAME= '" & v_table_name & "' and CONSTRAINT_TYPE<>'C' "
sqls = sqls & ") pivot (max(CONSTRAINT_TYPE) for CONSTRAINT_TYPE in ('U' as c_unique,'P' as c_primary,'R' as c_rela)) "
sqls = sqls & ") t6 "
sqls = sqls & "on t1.owner=t6.owner and t6.TABLE_NAME=t1.TABLE_NAME and t1.column_name=t6.column_name "
sqls = sqls & "where t1.OWNER= '" & v_owner & "' and t1.TABLE_NAME= '" & v_table_name & "' ORDER BY T1.COLUMN_ID " '注意传参

'On Error GoTo excetpion
Set rst = conn.Execute(sqls)

Do While Not rst.EOF()
	
  'Next 下一行
    rst.MoveNext
    num = num + 1
Loop '结束每行数据的处理