各类数据库通过sql查询表字段的注释
来源:互联网 发布:centos 7安装mysql 编辑:程序博客网 时间:2024/06/05 05:27
如果要写代码生成器,肯定会需要查询表字段与字段的注释。不然生成的代码还需要很多手动的操作。但由于各类数据库的系统表结构不一样,因此针对不同类型的查询sql也是不一样的。
oracle:
SELECT A.TABLE_NAME,A.COMMENTS,B.COLUMN_NAME,B.COMMENTS FROM USER_TAB_COMMENTS A,USER_COL_COMMENTS B WHERE A.TABLE_NAME=B.TABLE_NAME and a.table_name='SYS_TIME'
sqlserver2000:
select sc.name as columnName,sp.value as remarks from sysobjects so left outer join syscolumns sc on so.id = sc.id left outer join sysproperties sp on sc.id = sp.id and sc.colid = sp.smallid where so.type = 'u' and so.name='$tableName$' order by so.id, sc.colorder
sqlserver2005:
SELECT columnName=A.NAME, remarks=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 sys.extended_properties G ON A.ID=G.major_id AND A.COLID=G.minor_id LEFT JOIN sys.extended_properties F
ON D.ID=F.major_id AND F.minor_id=0 where D.NAME='$tableName$' ORDER BY A.ID,A.COLORDER
sqlserver2008:
SELECT a.name columnName, ISNULL(g.value,'') AS remarks FROM syscolumns a LEFT JOIN systypes b ON a.xtype=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 dbo.sysproperties g
ON d.id=g.id AND a.colid = g.smallid WHERE d.name='$tableName$' ORDER BY a.id,a.colorder
mysql:
select table_name,table_comment from information_schema.tables where table_schema = 'db' and table_name ='tablename'
oracle:
SELECT A.TABLE_NAME,A.COMMENTS,B.COLUMN_NAME,B.COMMENTS FROM USER_TAB_COMMENTS A,USER_COL_COMMENTS B WHERE A.TABLE_NAME=B.TABLE_NAME and a.table_name='SYS_TIME'
sqlserver2000:
select sc.name as columnName,sp.value as remarks from sysobjects so left outer join syscolumns sc on so.id = sc.id left outer join sysproperties sp on sc.id = sp.id and sc.colid = sp.smallid where so.type = 'u' and so.name='$tableName$' order by so.id, sc.colorder
sqlserver2005:
SELECT columnName=A.NAME, remarks=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 sys.extended_properties G ON A.ID=G.major_id AND A.COLID=G.minor_id LEFT JOIN sys.extended_properties F
ON D.ID=F.major_id AND F.minor_id=0 where D.NAME='$tableName$' ORDER BY A.ID,A.COLORDER
sqlserver2008:
SELECT a.name columnName, ISNULL(g.value,'') AS remarks FROM syscolumns a LEFT JOIN systypes b ON a.xtype=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 dbo.sysproperties g
ON d.id=g.id AND a.colid = g.smallid WHERE d.name='$tableName$' ORDER BY a.id,a.colorder
mysql:
select table_name,table_comment from information_schema.tables where table_schema = 'db' and table_name ='tablename'
- 各类数据库通过sql查询表字段的注释
- Oracle 查询表字段 注释 以及表的注释
- SQL 查询表结构信息+表字段注释
- sql 查询表字段的说明
- sql 查询表字段相同的数据
- SQL 修改表字段注释
- sql server 表字段查询
- sql语句查询表字段
- SQL 查询表字段信息
- oracle数据库查询表字段
- sql 查询所有数据库、表名、表字段总结
- sql 查询所有数据库、表名、表字段总结
- sql ---查询所有数据库、表名、表字段总结
- sql 查询所有数据库、表名、表字段总结
- SQL 查询所有数据库,表名,表字段总结
- sql 查询所有数据库、表名、表字段总结
- sql 查询所有数据库、表名、表字段总结
- oracle查询表字段信息(包含注释)
- android:imageview学习记录
- 运行自己的android应用
- 一次显示图片
- linux下的线程及同步机制(2)
- 每天学习,每天有进步,不虚浮,不彷徨是件多么快乐的事啊
- 各类数据库通过sql查询表字段的注释
- 网站记录
- 并发管理
- powerpoint2007背景音乐不中断
- windows 8 快捷键汇总
- 互联网行业的那些缩写PM,RD,FE,UE,QA,OP,BRD,MRD,PRD,FSD
- hdu 1033 Edge
- java程序使用纯JDBC方式操作SQLServer2005数据库(无需配置数据源)
- typeof 与 instanceof