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

目录

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

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

查询所有表格：

selec编程客栈t 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 sy编程客栈s.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 ,      
    srrZyGqscale , Nullable = case when is_nullable = 1 then 'Y' else 'N' end , mm.text as [default] ,      
   IsPkwww.devze.com = CASE WHEN i.index_id is not null THEN 1 ELSE 0 END      
from sysobjects s with(nolock) inner join sys.colu编程客栈mns 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;

到此这篇关于SQL Server查询所有表格以及字段的文章就介绍到这了,更多相关SQL Server查询所有表格内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！