sql语句查询数据库表结构信息

开发中经常用到查询指定表及其字段的信息，以下是我整理的SQL语句查询方法，供自己平时使用也提供给大家参考！ 1.适用MS SQL SERVER: 1 SELECT 2 表名 = case when a.colorder= 1 then d.name else '' end, 3 表说明 = case when a.colorder= 1 then isnull(

开发中经常用到查询指定表及其字段的信息，以下是我整理的SQL语句查询方法，供自己平时使用也提供给大家参考！

1.适用MS SQL SERVER:

<span> 1</span> <span>SELECT
</span><span> 2</span> 表名 = <span>case</span> when a.colorder=<span>1</span> then d.name <span>else</span> <span>''</span><span> end,
</span><span> 3</span> 表说明 = <span>case</span> when a.colorder=<span>1</span> then isnull(f.value,<span>''</span>) <span>else</span> <span>''</span><span> end,
</span><span> 4</span> 字段序号 =<span> a.colorder,
</span><span> 5</span> 字段名 =<span> a.name,
</span><span> 6</span> 标识 = <span>case</span> when COLUMNPROPERTY( a.id,a.name,<span>'</span><span>IsIdentity</span><span>'</span>)=<span>1</span> then <span>'</span><span>√</span><span>'</span><span>else</span> <span>''</span><span> end,
</span><span> 7</span> 主键 = <span>case</span> when exists(SELECT <span>1</span> FROM sysobjects <span>where</span> xtype=<span>'</span><span>PK</span><span>'</span> and parent_obj=a.id and name <span>in</span><span> (
</span><span> 8</span> SELECT name FROM sysindexes WHERE indid <span>in</span><span>(
</span><span> 9</span> SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then <span>'</span><span>√</span><span>'</span> <span>else</span> <span>''</span><span> end,
</span><span>10</span> 类型 =<span> b.name,
</span><span>11</span> 占用字节数 =<span> a.length,
</span><span>12</span> 长度 = COLUMNPROPERTY(a.id,a.name,<span>'</span><span>PRECISION</span><span>'</span><span>),
</span><span>13</span> 小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,<span>'</span><span>Scale</span><span>'</span>),<span>0</span><span>),
</span><span>14</span> 允许空 = <span>case</span> when a.isnullable=<span>1</span> then <span>'</span><span>√</span><span>'</span><span>else</span> <span>''</span><span> end,
</span><span>15</span> 默认值 = isnull(e.text,<span>''</span><span>),
</span><span>16</span> 字段说明 = isnull(g.[value],<span>''</span><span>)
</span><span>17</span> <span>FROM
</span><span>18</span> <span>syscolumns a
</span><span>19</span> <span>left join
</span><span>20</span> <span>systypes b
</span><span>21</span> <span>on
</span><span>22</span> a.xusertype=<span>b.xusertype
</span><span>23</span> <span>inner join
</span><span>24</span> <span>sysobjects d
</span><span>25</span> <span>on
</span><span>26</span> a.id=d.id and d.xtype=<span>'</span><span>U</span><span>'</span> and d.name<span>'</span><span>dtproperties</span><span>'</span>
<span>27</span> <span>left join
</span><span>28</span> <span>syscomments e
</span><span>29</span> <span>on
</span><span>30</span> a.cdefault=<span>e.id
</span><span>31</span> <span>left join
</span><span>32</span> <span>sys.extended_properties g
</span><span>33</span> <span>on
</span><span>34</span> --a.id=g.id and a.colid=<span>g.smallid
</span><span>35</span> a.id=g.major_id and a.colid=<span>g.Minor_id
</span><span>36</span> <span>left join
</span><span>37</span> <span>sys.extended_properties f
</span><span>38</span> <span>on
</span><span>39</span> --d.id=f.id and f.smallid=<span>0</span>
<span>40</span> d.id=f.major_id and f.Minor_id=<span>0</span>
<span>41</span> <span>where</span>
<span>42</span> d.name=<span>'</span><span>表名</span><span>'</span> --<span>如果只查询指定表,加上此条件
</span><span>43</span> <span>order by
</span><span>44</span> a.id,a.colorder

2.适用ORACLE:

<span> 1</span> <span>SELECT
</span><span> 2</span> USER_TAB_COLS.TABLE_NAME <span>as</span><span> 表名,
</span><span> 3</span> user_tab_comments.comments <span>as</span><span> 表备注,
</span><span> 4</span> USER_TAB_COLS.COLUMN_ID <span>as</span><span> 列序号,
</span><span> 5</span> user_col_comments.comments <span>as</span><span> 列备注,
</span><span> 6</span> USER_TAB_COLS.COLUMN_NAME <span>as</span><span> 列名 ,
</span><span> 7</span> USER_TAB_COLS.DATA_TYPE <span>as</span><span> 数据类型,
</span><span> 8</span> USER_TAB_COLS.DATA_LENGTH <span>as</span><span> 长度,
</span><span> 9</span> USER_TAB_COLS.NULLABLE <span>as</span><span> 是否为空,
</span><span>10</span> user_cons_columns.constraint_name <span>as</span><span> 约束名,
</span><span>11</span> user_constraints.constraint_type <span>as</span><span> 主键
</span><span>12</span> <span>FROM USER_TAB_COLS inner join user_col_comments on
</span><span>13</span> user_col_comments.TABLE_NAME=<span>USER_TAB_COLS.TABLE_NAME
</span><span>14</span> and user_col_comments.COLUMN_NAME=<span>USER_TAB_COLS.COLUMN_NAME
</span><span>15</span> INNER join user_cons_columns on user_cons_columns.table_name=<span>USER_TAB_COLS.table_name
</span><span>16</span> INNER join user_constraints on user_constraints.table_name=USER_TAB_COLS.table_name and user_constraints.constraint_name=<span>user_cons_columns.constraint_name
</span><span>17</span> inner join user_tab_comments on USER_TAB_COLS.TABLE_NAME=<span>user_tab_comments.TABLE_NAME
</span><span>18</span> WHERE USER_TAB_COLS.table_name=<span>'</span><span>表名</span><span>'</span>
<span>19</span> ORDER BY USER_TAB_COLS.TABLE_NAME

原文其它网址：http://www.zuowenjun.cn/post/2014/08/28/26.html