SQL Server查询所有表格以及字段

查询所有表格：

SELECT  CONVERT(varchar(64), s.id) AS TableId, s.name AS TableName, ISNULL(CAST(xp.value AS nvarchar(4000)), s.name) 
                   AS TableDesc, s.xtype AS TableType, s.crdate AS CreateTime
FROM      sys.sysobjects AS s WITH (nolock) LEFT OUTER JOIN
                   sys.extended_properties AS xp WITH (nolock) ON s.xtype = 'u' AND xp.class = 1 AND xp.minor_id = 0 AND 
                   xp.major_id = s.id AND xp.name IN (N'MS_Description')
WHERE   (s.xtype IN ('u', 'v'))

 

查询所有字段：

select 
    col.[object_id] as TableId
    , s.name as TableName
    , col.column_id as ColumnId
    ,col.name as ColumnName
    , IsNull(cast(xp.[value] as nvarchar(4000))
    , col.name) as [ColumnDesc] 
    , DataType = type_name(col.user_type_id) 
    , col.max_length AS 'DataLength'
    , Nullable = case when is_nullable = 1 then 'Y' else 'N' end 
    , mm.text as [DefaultValue] 
    , IsPk = CASE WHEN i.index_id is not null THEN 1 ELSE 0 END      
from sysobjects s with(nolock) inner join sys.columns col with(nolock) on s.id = col.[object_id]     
    left join sys.extended_properties xp with(nolock)       
     on  xp.class = 1 and  xp.minor_id > 0 and  xp.major_id = col.[object_id]       
     and xp.name in (N'MS_Description') and COL_NAME(xp.major_id, xp.minor_id) = col.name      
   left join sys.syscomments mm with(nolock) on mm.id = col.default_object_id      
   LEFT JOIN sys.indexes i with(nolock) ON i.[object_id] = col.[object_id]      
     AND (i.is_unique = 1 OR i.is_primary_key = 1 or i.is_unique_constraint = 1)      
     AND (index_col(s.name, i.index_id,1)=col.name or      
          index_col(s.name, i.index_id,2)=col.name or      
          index_col(s.name, i.index_id,3)=col.name      
         )      
where s.xtype in ('u' , 'v') and  col.object_id = OBJECT_ID('dt_inter') 

 

  查询指定表的所有字段：

SELECT
    TableName = d.name,--表名
    TableDesc = isnull(f.value, '') ,--表说明
    ColumnIndex = a.colorder,--字段序号
    ColumnName = a.name,--字段名
    IsIdentity = case when COLUMNPROPERTY(a.id, a.name, 'IsIdentity')= 1 then '√'else '×' end,--标识
    IsPrimaryKey = case when exists(SELECT 1 FROM sysobjects where xtype = 'PK' 
               and parent_obj = a.id 
               and name in (
               SELECT name FROM sysindexes 
               WHERE indid in(SELECT indid FROM sysindexkeys 
               WHERE id = a.id AND colid = a.colid))) then '√' else '×' end,--主键
    DataType = b.name,--类型
    ByteType = a.length,--占用字节数
    DataLength = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),--长度
    DataScale = isnull(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0),--小数位数
    Nullable = case when a.isnullable = 1 then '√'else '×' end,--允许空
    DefaultValue = isnull(e.text, ''),--默认值
    ColumnDesc = isnull(g.[value], '')--字段说明
FROM
    syscolumns a
left join
    systypes b
on
    a.xusertype = b.xusertype
inner join
    sysobjects d
on
    a.id = d.id  and d.xtype = 'U'
left join
    syscomments e
on
    a.cdefault = e.id
left join
sys.extended_properties g
on
    a.id = G.major_id and a.colid = g.minor_id
left join
sys.extended_properties f
on
    d.id = f.major_id and f.minor_id = 0
where
    d.name = 'dt_inter'--如果只查询指定表,加上此where条件，tablename是要查询的表名；去除where条件查询所有的表信息
order by
    a.id,a.colorder

 查询结构：