SQL Server query table, table Description, association table, field description, statement summary

-- --Query all tables SELECT *  from WHERE TYPE='U'

----Queries all field names and their comments based on the table name   SELECT a.name,b.value from    syscolumns A left  JOIN SYS. Extended_properties B on a.id=b.major_id and a.colid=b.minor_id INNER joins SYSOBJECTS    C on A.id=c.id and UPPER (c.name ) = ' Tb_name '

------Query All of the table's associated tables based on the table nameSELECTPrimary key Column ID=B.rkey, primary key column name=(SELECTName fromsyscolumnsWHEREColid=B.rkey andId=B.rkeyid), foreign key table ID=B.fkeyid, foreign key table name=object_name(B.fkeyid), foreign key column ID=B.fkey, foreign key column name=(SELECTName fromsyscolumnsWHEREColid=B.fkey andId=B.fkeyid), cascade update=ObjectProperty(a.ID,'Cnstisupdatecascade'), cascade delete=ObjectProperty(a.ID,'Cnstisdeletecascade')  fromsysobjects aJoinSysforeignkeys b ona.ID=B.constidJoinsysobjects C onA.parent_obj=c.idwhereA.xtype='F'  andC.xtype='U'      and object_name(B.rkeyid)=' Tb_name'     ----Main Table table name    

Table Description----Query association table, primary key column name, outer table name, foreign key column name     SELECT     table Description     =  IsNull (d.value,object_name (B.fkeyid))     , primary key column name = ( Select name from syscolumns where Colid=b.rkey and Id=b.rkeyid)      , foreign key table name =object_name (B.fkeyid)     , foreign key column name = (SELECT Name from syscolumns WHERE Colid=b.fkey and Id=b.fkeyid) from sysobjects a     joins Sysforeignkeys B on A.ID=B.CONSTID
   join sysobjects C on a.parent_obj=c.id left     joins sys.extended_properties d on d.minor_id = 0 and c.id = D.MAJOR_IDW Here a.xtype= ' F ' and c.xtype= ' U ' and     object_name (B.rkeyid) = ' Tb_name '     

　　

------The most complete table information query   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'  andParent_obj=a.ID 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 whereD.name=' Tb_name' Order  byA.id,a.colorder

　

SQL Server query table, table Description, association table, field description, statement summary