IT俱乐部 MsSql SQL Server查询所有表格及字段的示例代码

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

发布: 2024年 9月 8日 519阅读

目录

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;

到此这篇关于SQL Server查询所有表格以及字段的文章就介绍到这了,更多相关SQL Server查询所有表格内容请搜索IT俱乐部以前的文章或继续浏览下面的相关文章希望大家以后多多支持IT俱乐部！

本文收集自网络，不代表IT俱乐部立场，转载请注明出处。https://www.2it.club/database/mssql/13370.html

115赞

标签:server sql 字段示例表格