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

查询所有表格：

select convert(varchar(64),s.id) as fRowId, s.name as TableName    
 , IsNull(cast(xp.[value] as nvarchar(4000)), s.name) as TableDesc        
    , ModuleCode = CONVERT(varchar(16),case when s.name like 't%' then SUBSTRING(s.name,2,3)    
         when SUBSTRING(s.name,4,1) = '_' then substring(s.name,1,3)     
         else '' end)        
    , fCreateTime = s.crdate  
from sysobjects s with(nolock)         
    left join sys.extended_properties 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 , col.name  
    , IsNull(cast(xp.[value] as nvarchar(4000)), col.name) as [desc] ,       
    TypeName = type_name(col.user_type_id) ,       
    Prec = case when type_name(col.user_type_id) in ('nvarchar','nchar') then col.max_length/2       
           when col.precision = 0 then col.max_length else col.precision end ,       
    scale , Nullable = case when is_nullable = 1 then 'Y' else 'N' end , mm.text as [default] ,       
   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')

 

根据表格名称，查询所有字段：

SELECT 
    c.name AS 'Column Name',
    t.name AS 'Data Type',
    c.max_length AS 'Length',
    ISNULL(ep.value, '') AS 'Description'
FROM 
    sys.columns c
LEFT JOIN 
    sys.types t ON c.system_type_id = t.system_type_id
LEFT JOIN 
    sys.extended_properties ep ON c.object_id = ep.major_id AND c.column_id = ep.minor_id
WHERE 
    c.object_id = OBJECT_ID('tbmslevel') -- Replace with your table name
ORDER BY 
    c.column_id;