所有数据库类型(oracle、sql server、 db2、 mysql……)读表的表结构方法都一样么?
如果不同应该怎么去读,本菜鸟只找到怎么读oracle表结构的代码如下:SELECT
M.column_name FieldName, M.data_type FieldType, M.data_length FieldSize, M.data_precision LENGTH,
M.Data_Scale Scale, M.nullable IsNul, M.Data_default DefaultValue, A.comments Note,
(CASE WHEN B.column_name IS NULL THEN 'N' ELSE 'Y' END) MainKey
FROM
(SELECT * FROM user_tab_columns WHERE UPPER(Table_name) = UPPER('tablename')) M
LEFT JOIN user_col_comments A
ON M.COLUMN_NAME=A.COLUMN_NAME
AND M.Table_Name = A.Table_Name
LEFT JOIN (SELECT a.table_name,b.column_name FROM user_constraints a, user_cons_columns b
WHERE a.constraint_name=b.constraint_name
AND UPPER(a.table_name) = UPPER('tablename')
AND a.constraint_type='P') B
ON M.Table_Name = B.TABLE_NAME AND M.COLUMN_NAME=B.COLUMN_NAME ORDER BY M.column_id如果不一样,恳请各位牛人帮忙给出读别的数据库的表结构的思路
如果不同应该怎么去读,本菜鸟只找到怎么读oracle表结构的代码如下:SELECT
M.column_name FieldName, M.data_type FieldType, M.data_length FieldSize, M.data_precision LENGTH,
M.Data_Scale Scale, M.nullable IsNul, M.Data_default DefaultValue, A.comments Note,
(CASE WHEN B.column_name IS NULL THEN 'N' ELSE 'Y' END) MainKey
FROM
(SELECT * FROM user_tab_columns WHERE UPPER(Table_name) = UPPER('tablename')) M
LEFT JOIN user_col_comments A
ON M.COLUMN_NAME=A.COLUMN_NAME
AND M.Table_Name = A.Table_Name
LEFT JOIN (SELECT a.table_name,b.column_name FROM user_constraints a, user_cons_columns b
WHERE a.constraint_name=b.constraint_name
AND UPPER(a.table_name) = UPPER('tablename')
AND a.constraint_type='P') B
ON M.Table_Name = B.TABLE_NAME AND M.COLUMN_NAME=B.COLUMN_NAME ORDER BY M.column_id如果不一样,恳请各位牛人帮忙给出读别的数据库的表结构的思路
--SQL2000读取表结构
SELECT 表名=case when a.colorder=1 then d.name else '' end,
表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '√' else '' end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case when a.isnullable=1 then '√'else '' end,
默认值=isnull(e.text,''),
字段说明=isnull(g.[value],'')
FROM syscolumns a
left join systypes b on a.xusertype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id and a.colid=g.smallid
left join sysproperties f on d.id=f.id and f.smallid=0
我没有那个意思,只是开始发在MS-SQL那个版有点冷清,而又急着等用,所以就发到了ORACLE这个版块来了
还有一个问题要问,就是怎么判断SQL语句中的的字段是不是关键字呀?
就是判断where、and、 on、 substr……这些有米有系统函数调用的?
to 6楼:一般把sql写在plsql这样的工具里,写关键字都会变颜色的
就是说,在一个SQL语句里,有没有系统函数自动识别关键字的,
如果没有是否把所有关键字都罗列出来用IN去判断?
所以读表结构的语句估计不能通用...对于关键字,像pl/sql dev这种工具我估计也是用罗列的...一般变化不大吧.