我要评论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查询所有表格内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论