2023年6月23日发(作者：)

sql语句查询数据库表结构信息开发中经常⽤到查询指定表及其字段的信息，以下是我整理的SQL语句查询⽅法，供⾃⼰平时使⽤也提供给⼤家参考！1.适⽤MS SQL SERVER: 1 SELECT 2 表名 = case when er=1 then else '' end, 3 表说明 = case when er=1 then isnull(,'') else '' end, 4 字段序号 = er, 5 字段名 = , 6 标识 = case when COLUMNPROPERTY( ,,'IsIdentity')=1 then '√'else '' end, 7 主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj= and name in ( 8 SELECT name FROM sysindexes WHERE indid in( 9 SELECT indid FROM sysindexkeys WHERE id = AND colid=))) then '√' else '' end,10 类型 = ,11 占⽤字节数 = ,12 长度 = COLUMNPROPERTY(,,'PRECISION'),13 ⼩数位数 = isnull(COLUMNPROPERTY(,,'Scale'),0),14 允许空 = case when able=1 then '√'else '' end,15 默认值 = isnull(,''),16 字段说明 = isnull(g.[value],'')17 FROM18 syscolumns a19 left join20 systypes b21 on22 ype=ype23 inner join24 sysobjects d25 on26 = and ='U' and <>'dtproperties'27 left join28 syscomments e29 on30 lt=31 left join32 ed_properties g33 on34 --= and =d35 =_id and =_id36 left join37 ed_properties f38 on39 --= and d=040 =_id and _id=041 where42 ='表名' --如果只查询指定表,加上此条件43 order by44 ,er2.适⽤ORACLE: 1 SELECT 2 USER_TAB__NAME as 表名, 3 user_tab_ts as 表备注, 4 USER_TAB__ID as 列序号, 5 user_col_ts as 列备注, 6 USER_TAB__NAME as 列名 , 7 USER_TAB__TYPE as 数据类型, 8 USER_TAB__LENGTH as 长度, 9 USER_TAB_LE as 是否为空,10 user_cons_aint_name as 约束名,11 user_aint_type as 主键12 FROM USER_TAB_COLS inner join user_col_comments on13 user_col__NAME=USER_TAB__NAME14 and user_col__NAME=USER_TAB__NAME15 INNER join user_cons_columns on user_cons__name=USER_TAB__name16 INNER join user_constraints on user__name=USER_TAB__name and user_aint_name=user_cons_aint_name17 inner join user_tab_comments on USER_TAB__NAME=user_tab__NAME18 WHERE USER_TAB__name='表名'19 ORDER BY USER_TAB__NAME原⽂其它⽹址：