SQL语句获取数据表名,字段等信息
代码
1 ''ORACLE:
2 ''获取表名
3
4 strSql = ""
5 strSql = " select user_tables.TABLE_NAME,USER_OBJECTS.CREATED from user_tables,USER_OBJECTS"
6 strSql = strSql & " where USER_OBJECTS.OBJECT_NAME=user_tables.TABLE_NAME order by USER_OBJECTS.CREATED"
7
8 ''获取字段信息
9 szSql = "SELECT COLUMN_NAME,DATA_TYPE,DATA_PRECISION,DATA_SCALE,NULLABLE," & _
10 "DATA_LENGTH FROM user_tab_columns WHERE table_name ='" & szTable & "' order by COLUMN_ID"
11
12 ''获取主键字段信息
13 szSql = ""
14 szSql = "select col.column_namE from user_constraints con, user_cons_columns co l" Where con.constraint_name = col.constraint_name and con.constraint_type='P' and col.table_name = '" & szTable & "' order by position"
2 ''获取表名
3
4 strSql = ""
5 strSql = " select user_tables.TABLE_NAME,USER_OBJECTS.CREATED from user_tables,USER_OBJECTS"
6 strSql = strSql & " where USER_OBJECTS.OBJECT_NAME=user_tables.TABLE_NAME order by USER_OBJECTS.CREATED"
7
8 ''获取字段信息
9 szSql = "SELECT COLUMN_NAME,DATA_TYPE,DATA_PRECISION,DATA_SCALE,NULLABLE," & _
10 "DATA_LENGTH FROM user_tab_columns WHERE table_name ='" & szTable & "' order by COLUMN_ID"
11
12 ''获取主键字段信息
13 szSql = ""
14 szSql = "select col.column_namE from user_constraints con, user_cons_columns co l" Where con.constraint_name = col.constraint_name and con.constraint_type='P' and col.table_name = '" & szTable & "' order by position"