SQLServer导出数据库字典的详细图文教程

【字号：大中小】日期：2023-02-22 14:52:30浏览：7作者：猪猪

SQL

直接修改带注释那行的表名称即可。

SELECT  
表名=case when a.colorder=1 then d.name else "" end, 
表说明=case when a.colorder=1 then isnull(f.value,"") else "" end,
字段序号=a.colorder, 
字段名=a.name, 
标识=case when COLUMNPROPERTY(a.id,a.name,"IsIdentity")=1 then "√"else "" end, 
主键=case when exists(SELECT 1 FROM sysobjects where xtype="PK" and name in (
  SELECT name FROM sysindexes WHERE indid in(
   SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid 
   ))) then "√" else "" end, 
类型=b.name, 
占用字节数=a.length, 
长度=COLUMNPROPERTY(a.id,a.name,"PRECISION"), 
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,"Scale"),0), 
允许空=case when a.isnullable=1 then "√"else "" end, 
默认值=isnull(e.text,""), 
字段说明=isnull(g.[value],"") 
FROM syscolumns a 
left join systypes b on a.xtype=b.xusertype 
inner join sysobjects d on a.id=d.id and d.xtype="U" and d.name<>"dtproperties" 
left join syscomments e on a.cdefault=e.id 
left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id 
left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0 
where d.name="girlSix" --如果只查询指定表,加上此条件  
order by a.id,a.colorder

测试sql

CREATE TABLE [dbo].[girlSix] (
[id] varchar(32) NOT NULL DEFAULT (replace(newid(),"-","")) ,
[createDate] datetime NOT NULL DEFAULT (getdate()) ,
[nickName] varchar(30) NOT NULL ,
[introduce] nvarchar(200) NOT NULL 
)
 
 
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty("MS_Description", 
"SCHEMA", N"dbo", 
"TABLE", N"girlSix", 
"COLUMN", N"nickName")) > 0) 
EXEC sp_updateextendedproperty @name = N"MS_Description", @value = N"名字"
, @level0type = "SCHEMA", @level0name = N"dbo"
, @level1type = "TABLE", @level1name = N"girlSix"
, @level2type = "COLUMN", @level2name = N"nickName"
ELSE
EXEC sp_addextendedproperty @name = N"MS_Description", @value = N"名字"
, @level0type = "SCHEMA", @level0name = N"dbo"
, @level1type = "TABLE", @level1name = N"girlSix"
, @level2type = "COLUMN", @level2name = N"nickName"
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty("MS_Description", 
"SCHEMA", N"dbo", 
"TABLE", N"girlSix", 
"COLUMN", N"introduce")) > 0) 
EXEC sp_updateextendedproperty @name = N"MS_Description", @value = N"介绍"
, @level0type = "SCHEMA", @level0name = N"dbo"
, @level1type = "TABLE", @level1name = N"girlSix"
, @level2type = "COLUMN", @level2name = N"introduce"
ELSE
EXEC sp_addextendedproperty @name = N"MS_Description", @value = N"介绍"
, @level0type = "SCHEMA", @level0name = N"dbo"
, @level1type = "TABLE", @level1name = N"girlSix"
, @level2type = "COLUMN", @level2name = N"introduce"
GO
 
-- ----------------------------
-- Records of girlSix
-- ----------------------------
INSERT INTO [dbo].[girlSix] ([id], [createDate], [nickName], [introduce]) VALUES (N"04e3d962adcb4a5b8fefaf8b46995e85", N"2020-05-27 09:05:52.000", N"董新颖", N"郭老师关门弟子之一。");
GO
INSERT INTO [dbo].[girlSix] ([id], [createDate], [nickName], [introduce]) VALUES (N"568fc305930347d3bec1ddd08c71ad29", N"2020-05-27 09:01:09.000", N"王笑涵", N"北方有佳人，绝世而独立。");
GO
INSERT INTO [dbo].[girlSix] ([id], [createDate], [nickName], [introduce]) VALUES (N"972ec358089042e0bf24fd9efca47bde", N"2020-05-27 08:59:49.000", N"牛龙珠", N"笑若桃花三月开，清风徐徐醉颜来。");
GO
INSERT INTO [dbo].[girlSix] ([id], [createDate], [nickName], [introduce]) VALUES (N"BDFFC6A36A53408281EB8CA242C0E7A3", N"2020-05-27 08:42:31.000", N"闫春娜", N"珠缨旋转星宿摇,花蔓抖擞龙蛇动。");
GO
INSERT INTO [dbo].[girlSix] ([id], [createDate], [nickName], [introduce]) VALUES (N"efb0ca854dac456b9d8c42d4c4b1bce0", N"2020-05-27 09:03:30.000", N"刘梓佳", N"明眸善睐，辅靥承权，瑰姿艳逸，怡静体闲，端的是好一个花王，富贵的牡丹。");
GO
INSERT INTO [dbo].[girlSix] ([id], [createDate], [nickName], [introduce]) VALUES (N"f839343b980e45caafaa9d2c9797294b", N"2020-05-27 09:04:53.000", N"魏慧娟", N"脉脉眼中波，盈盈花盛处。");
GO
 
-- ----------------------------
-- Indexes structure for table girlSix
-- ----------------------------
 
-- ----------------------------
-- Primary Key structure for table [dbo].[girlSix]
-- ----------------------------
ALTER TABLE [dbo].[girlSix] ADD PRIMARY KEY ([id])
GO

执行效果