Get table information in SQL 2012, containing a description of the field

1. Get the tables in the database

Select  from where type='U'

2. Get table fields (here is the route table)

Select  from Where ID=object_id('Route')

3. Get a description of the fields in the table

SELECT   Value from     :: Fn_listextendedproperty (NULL'user' dbo ' ' Table ' ' Route ' ' column ' default)

4. Get all information about the table information such as

SelectB.[value]  fromSys.columns A Left JoinSys.extended_properties b onA.object_id=b.major_id anda.column_id=b.minor_idInner Joinsysobjects C ona.column_id=c.id andA.[name]='Column Name'  andC.[name]='Table name'SELECTTable name= Case    whenA.colorder=1    ThenD.nameElse   "'   End, table describes= Case    whenA.colorder=1    Then   IsNull(F.value,"')Else   "'   End, Field ordinal=a.colorder, field name=a.name, Logo= Case    when   ColumnProperty(A.id,a.name,'isidentity')=1    Then   '√'Else   "'   End, PRIMARY key= Case    when   exists(SELECT   1    fromsysobjectswhereXtype='PK'    andNameinch   (SELECTName fromsysindexesWHEREIndidinch(SELECTIndid fromSysindexkeysWHEREId=a.ID andColid=a.colid ))) Then   '√'   Else   "'   End, type=b.name, number of bytes occupied=a.length, Length=ColumnProperty(A.id,a.name,'PRECISION'), number of decimal digits=IsNull(ColumnProperty(A.id,a.name,' Scale'),0), allow null= Case    whenA.isnullable=1    Then   '√'Else   "'   End, the default value=IsNull(E.text,"'), field description=IsNull(g.[value],"') fromsyscolumns a Left   JoinSystypes b onA.xusertype=B.xusertypeInner   Joinsysobjects D ona.ID=D.id andD.xtype='U'    andD.name<>'dtproperties' Left   Joinsyscomments E onA.cdefault=e.id Left   JoinSys.extended_properties g ona.ID=g.major_id andA.colid=g.minor_id Left   JoinSys.extended_properties F onD.id=f.major_id andf.minor_id=0--where d.name= ' orders '--if only the specified table is queried, add this conditionOrder    byA.id,a.colorder

Such as

5. Get the table name, field name, data type, and field description of a table for shorthand sql:

SELECT     [TableName] =I_s.table_name,[ColumnName] =I_s.column_name,[Description] =S.value,[DataType]=I_s.data_type frominformation_schema. COLUMNS i_s Left OUTER JOINsys.extended_properties S ons.major_id= object_id(I_s.table_schema+'.'+i_s.table_name) ands.minor_id=i_s.ordinal_position andS.name= 'ms_description' WHERE     ObjectProperty(object_id(I_s.table_schema+'.'+I_s.table_name),'ismsshipped')=0      andI_s.table_name= 'Table name'  andI_s.table_schema='Schema name'ORDER  byI_s.table_name, I_s.ordinal_position

Get table information in SQL 2012, containing a description of the field