SQL statement query database table structure information

Source: Internet
Author: User
In development, it is often used to query the information of a specified table and its fields. The following is an SQL statement query method that I have compiled for your use at ordinary times! 1. MSSQLSERVER: 1SELECT2 table name casewhena. colorder1thend. nameelseend. 3 table description casewhena. colorder1thenisnull (

In development, it is often used to query the information of a specified table and its fields. The following is an SQL statement query method that I have compiled for your use at ordinary times! 1. applicable to ms SQL SERVER: 1 SELECT 2 table name = case when. colorder = 1 then d. name else ''end, 3 table description = case when. colorder = 1 then isnull (

In development, it is often used to query the information of a specified table and its fields. The following is an SQL statement query method that I have compiled for your use at ordinary times!

1. Applicable to ms SQL SERVER:

1 SELECT 2 table name = case when. colorder = 1 then d. name else ''end, 3 table description = case when. colorder = 1 then isnull (f. value, '') else'' end, 4 Field No. =. colorder, 5 Field name =. name, 6 id = case when COLUMNPROPERTY (. id,. name, 'isidentity ') = 1 then' √ 'else' end, 7 primary key = case when exists (SELECT 1 FROM sysobjects where xtype = 'pk' and parent_obj =. id and name in (8 SELECT name FROM sysindexes WHERE indid in (9 SELECT indid FROM sysindexkeys WHERE id =. id AND colid =. colid) then '√ 'else' 'end, 10 type = B. name, 11 bytes occupied =. length, 12 length = COLUMNPROPERTY (. id,. name, 'precision '), 13 decimal places = isnull (COLUMNPROPERTY (. id,. name, 'Scale'), 0), 14 can be empty = case when. isnullable = 1 then' √ 'else' end, 15 default value = isnull (e. text, ''), 16 Field description = isnull (g. [value], '') 17 FROM18 syscolumns a19 left join20 policypes b21 on22. xusertype = B. xusertype23 inner join24 sysobjects d25 on26. id = d. id and d. xtype = 'U' and d. name <> 'dtproperties' 27 left join28 syscomments e29 on30. cdefault = e. id31 left join32 sys. extended_properties g33 on34 --. id = g. id and. colid = g. smal000035. id = g. major_id and. colid = g. minor_id36 left join37 sys. extended_properties f38 on39 -- d. id = f. id and f. smallid = 040 d. id = f. major_id and f. minor_id = 041 where42 d. name = 'table name' -- if only the specified table is queried, add this condition 43 order by44. id,. colorder

2. Applicable ORACLE:

1 SELECT 2 USER_TAB_COLS.TABLE_NAME as table name, 3 user_tab_comments.comments as table remarks, 4 comment as column number, 5 user_col_comments.comments as column remarks, 6 USER_TAB_COLS.COLUMN_NAME as column name, 7 comment as data type, 8 USER_TAB_COLS.DATA_LENGTH as length, 9 USER_TAB_COLS.NULLABLE as is empty, 10 user_cons_columns.constraint_name as constraint name, 11 primary as primary key 12 FROM USER_TAB_COLS inner join user_col_comments on13 partitions = internal and external = internal INNER join user_cons_columns on partitions = internal INNER join user_constraints on partitions = internal and external = internal inner join user_tab_comments USER_TAB_COLS.TABLE_NAME = user_tab_comments.TABLE_NAME18 WHERE USER_TAB_COLS.table_name = 'table name' 19 order by USER_TAB_COLS.TABLE_NAME

Other Website: http://www.zuowenjun.cn/post/2014/08/28/26.html

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.