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