Sql server 获取数据库表和字段信息

--得到表和视图
select id as ID,name  as TableName,xtype as Type,
(
    SELECT isnull(g.[value],'-')
    FROM sys.extended_properties g
    where g.major_id=id and g.minor_id=0
) as TableRemark
from sysobjects
where (xtype='U' or xtype='V') and name<>'dtproperties' order by xtype,name

--得到某个表中所有字段名：

SELECT syscolumns.colorder as ID,  sysobjects.name  as TableName,  syscolumns.name     AS     ColumnName,
(
    SELECT isnull(g.[value],'-') AS ColumnRemark
    FROM sys.columns a left join sys.extended_properties g
    on (a.object_id = g.major_id AND g.minor_id = a.column_id)
    WHERE object_id =(SELECT object_id FROM sys.tables WHERE name =sysobjects.name and a.Name=syscolumns.name)
) as ColumnRemark,
systypes.name AS  Type,syscolumns.length as Length,syscolumns.isnullable  as IsNullAble
FROM     sysobjects   
INNER     JOIN syscolumns     ON     sysobjects.id     =     syscolumns.id   
INNER     JOIN systypes     ON     syscolumns.xtype     =     systypes.xtype  
WHERE     (sysobjects.xtype     =     'U')   
    AND     (sysobjects.name     <>     'dtproperties')   
    AND     (sysobjects.name     =     'T_SYS_Page')   
    AND     (systypes.name     <>     'sysname')   
    AND     (systypes.status     <>     3)
order by  syscolumns.colorder