SQL语句查询数据库所有表和所有字段的详细信息（包括表描述
和字段描述）
select (case
lorder =1then
ddd.value
else
''
end
)                                                as "表名（中⽂）" --如果表名相同就返回空
, (case
lorder =1then
d.name
else
''
end
)                                                as表名--如果表名相同就返回空
, (case
lorder =1then
ddd.value
else
''
end
)
                                                as表说明--如果表名相同就返回空
, a.colorder                                      as字段序号
, a.name                                          as字段名
, (case
when columnproperty(a.id, a.name, 'IsIdentity') =1then
'√'
else
''
end
)                                                as是否⾃增标识
, (case
when
(
select count(*)
from sys.sysobjects --查询主键
where (name in
(
select name
from sys.sysindexes
where (id = a.id)
and (indid in
(
select indid
from sys.sysindexkeys
where (id = a.id)
and (colid in
(
select colid from sys.syscolumns where (id = a.id) and (name = a.name)
)
)
)
)
)
)
and (xtype ='PK')
) >0then
'√'
else
''
end
)                                                as主键--查询主键END
, b.name                                          as类型
, a.length                                        as占⽤字节数
, columnproperty(a.id, a.name, 'PRECISION')        as长度
, isnull(columnproperty(a.id, a.name, 'Scale'), 0) as⼩数位数
, (case
when a.isnullable =1then
'√'
else
''
end
)                                                as允许空
, , '')                              as默认值
, isnull(g.value, '')                              as字段说明
from sys.syscolumns                  a
left join sys.systypes            b
pe = b.xusertype
inner join sys.sysobjects        d
on a.id = d.id
pe ='U'
常用的sql查询语句有哪些and d.name <>'dtproperties'
left outer join
(
select major_id
, value
ded_properties
where name ='MS_Description'
and minor_id =0
)                                as ddd
on a.id = ddd.major_id
left join sys.syscomments        e
on a.cdefault = e.id
left ded_properties g
on a.id = g.major_id
lid = g.minor_id
order by a.id
, a.colorder;
ALTER PROC [dbo].[sp_helpremark]
@TABLE_NAME VARCHAR(50)
AS
SELECT
(case lorder=1 then ddd.value else '' end) as "表名（中⽂）",--如果表名相同就返回空
(case lorder=1 then d.name else '' end) as 表名,--如果表名相同就返回空
(case lorder=1 then ddd.value else '' end) as 表说明,--如果表名相同就返回空
a.name as 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) as 是否⾃增标识,      (case when (SELECT count(*) FROM sysobjects--查询主键
WHERE (name in
(SELECT name FROM sysindexes
WHERE (id = a.id)  AND (indid in
(SELECT indid FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid FROM syscolumns
WHERE (id = a.id) AND (name = a.name)
)
)
)
)
)
)
AND (xtype = 'PK'))>0 then '√' else '' end) as 主键,--查询主键END
b.name as 类型,
a.length as 占⽤字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as  长度,
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as ⼩数位数,
(case when a.isnullable=1 then '√'else '' end) as 允许空,
,'') as 默认值,
isnull(g.[value],'') AS 字段说明
FROM syscolumns a
left join systypes b
pe=b.xusertype
inner join sysobjects d
on a.id=d.id pe='U' and d.name<>'dtproperties'
LEFT OUTER JOIN( SELECT major_id, value
ded_properties
WHERE name='MS_Description' AND minor_id = 0)
as ddd  ON a.id = ddd.major_id
left join syscomments e
on a.cdefault=e.id
left ded_properties g
on a.id=g.major_id lid = g.minor_id  where d.name like '%'+@TABLE_NAME+'%'
order by a.lorder

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。