SQL Server查询所有表格及字段的示例代码

目录

• 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;