SQL Server获取表的字段和获取外键表及关联字段

1、获取相关表的字段名

select  object_name(OBJECT_ID) 表名, name 字段名
from    sys.columns
where   object_name(OBJECT_ID) in ( 'table1', 'table2' )
order by object_name(OBJECT_ID)

2、获取关联外键表和外键字段

select  t1.ftableName, t1.name, t2.rtableName, t2.name
from    ( select    object_name(f.fkeyid) as ftableName, col.name, f.constid as temp
          from      syscolumns col , sysforeignkeys f
          where     f.fkeyid = col.id and f.fkey = col.colid and f.constid in 
          ( select distinct ( id ) from sysobjects where object_name(parent_obj) = 'tb_Order' and xtype = 'F' )
        ) as t1 ,
        ( select    object_name(f.rkeyid) as rtableName, col.name, f.constid as temp
          from      syscolumns col ,sysforeignkeys f
          where     f.rkeyid = col.id and f.rkey = col.colid and f.constid in 
          ( select distinct ( id ) from  sysobjects where object_name(parent_obj) = 'tb_Order' and xtype = 'F' )
        ) as t2
where   t1.temp = t2.temp

3. 获取表非主外键的其他列

select  TABLENAME, COLUMNNAME, DATATYPE
from    ( select    object_name(OBJECT_ID) TABLENAME, NAME COLUMNNAME, USER_TYPE_ID DATATYPE
          from      SYS.COLUMNS
          where     IS_IDENTITY <> 1
        ) A
where   A.COLUMNNAME not in ( select    COL.NAME COLNAME
                              from      SYSCOLUMNS COL ,
                                        SYSFOREIGNKEYS F
                              where     F.FKEYID = COL.ID and F.FKEY = COL.COLID and F.CONSTID in 
                              ( select  ID from    SYSOBJECTS where   XTYPE = 'F' ) ) and A.TABLENAME = 'tb_Order'

4.取得Ms SqlServer数据库中某张表的所有列

select  sysobjects.name as TABLENAME, syscolumns.NAME as NAME, systypes.name VTYPE, syscolumns.LENGTH, syscolumns.XPREC, syscolumns.XSCALE, isnullable, cdefault
from    syscolumns ,
        sysobjects ,
        systypes
where   syscolumns.id = sysobjects.id and syscolumns.xtype = systypes.xtype and sysobjects.type = 'U' and sysobjects.name = '表名'

5.取得Ms Sqlserver数据库的所有外键信息

select  b.name as key_name, a.name as name, d.name as f_name
from    sysobjects a
join    sysobjects b on a.id = b.parent_obj
join    sysforeignkeys c on b.id = c.constid
join    sysobjects d on c.rkeyid = d.id
join    sysindexkeys e on d.id = e.id
join    syscolumns f on a.id = f.id and e.colid = f.colid
where   b.xtype = 'F'