SQL statements Query database table structure information

Development often use to query the specified table and its field information, the following is my SQL statement query method, for their usual use is also available to everyone reference!

1. For MS SQL SERVER:

1 SELECT2Table name = CaseWhen a.colorder=1Then D.nameElse "'End,3Table Description = CaseWhen a.colorder=1Then IsNull (F.value,"')Else "'End,4Field ordinal =A.colorder,5Field name =A.name,6Identity = CaseWhen ColumnProperty (A.id,a.name,'isidentity')=1Then'√'Else "'End,7Primary KEY = CaseWhen exists (SELECT1From sysobjectswhereXtype='PK'and Parent_obj=a.id and nameinch (8SELECT name from sysindexes WHERE indidinch(9SELECT indid from Sysindexkeys WHERE id = a.id and colid=a.colid))'√' Else "'End,TenType =B.name, OneNumber of bytes occupied =A.length, ALength = ColumnProperty (A.id,a.name,'PRECISION'), -Number of decimal digits = IsNull (ColumnProperty (A.id,a.name,' Scale'),0), -Allow NULL = CaseWhen a.isnullable=1Then'√'Else "'End, theDefault value = IsNull (E.text,"'), -Field Description = IsNull (G.[value],"') -  from - syscolumns a + Left join - systypes b +  on AA.xusertype=B.xusertype at INNER JOIN - sysobjects D -  on -A.id=d.id and D.xtype='U'and d.name<>'dtproperties' - Left join - syscomments e in  on -a.cdefault=e.id to Left join + sys.extended_properties G -  on the--a.id=g.id and A.colid=G.smallid *A.ID=G.MAJOR_ID and A.colid=g.minor_id $ Left joinPanax Notoginseng sys.extended_properties F -  on the--d.id=f.id and F.smallid=0 +D.ID=F.MAJOR_ID and F.minor_id=0 A where theD.name='Table name'--If you query only the specified table, add this condition + Order by -A.id,a.colorder

2. For Oracle:

1 SELECT2User_tab_cols. table_name astable name,3User_tab_comments.comments asTable notes,4User_tab_cols. column_id ascolumn number,5User_col_comments.comments ascolumn notes,6User_tab_cols. column_name ascolumn name,7User_tab_cols. Data_type asdata type,8User_tab_cols. Data_length aslength,9User_tab_cols. NULLABLE asis empty,TenUser_cons_columns.constraint_name asconstraint name, OneUser_constraints.constraint_type asPRIMARY Key A From user_tab_cols INNER join user_col_comments on -User_col_comments. Table_name=User_tab_cols. TABLE_NAME -and user_col_comments. Column_name=User_tab_cols. column_name theINNER Join User_cons_columns on User_cons_columns.table_name=User_tab_cols.table_name -INNER join user_constraints on User_constraints.table_name=user_tab_cols.table_name and User_constraints.constraint_ Name=User_cons_columns.constraint_name -INNER join user_tab_comments on User_tab_cols. Table_name=user_tab_comments. TABLE_NAME -WHERE user_tab_cols.table_name='Table name' +ORDER by User_tab_cols. table_name

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

SQL statements Query database table structure information