使用sp_addextendedproperty添加描述信息

最新推荐文章于 2023-07-03 17:17:46 发布
最新推荐文章于 2023-07-03 17:17:46 发布 · 91 阅读 · 0

本文介绍如何使用 SQL 的 sp_addextendedproperty 过程为表和列添加描述性注释，包括具体的执行语句示例。

-- For table

EXECUTE sp_addextendedproperty N'MS_Description', '描述内容', N'user', N'dbo', N'table', N'表名', NULL, NULL

-- For column

EXECUTE sp_addextendedproperty N'MS_Description', '描述内容', N'user', N'dbo', N'table', N'表名', N'column', N'字段名'

iteye_16215
关注
SQL server添加表和字段注释
大郎喝药的博客
09-01 2787
--加表注释写法: EXEC sp_addextendedproperty N'MS_Description', N'用户登录表', N'SCHEMA', N'dbo',N'TABLE', N'IN_ID_LFA_PERSISTENCY'; --例： EXEC sp_addextendedproperty N'MS_Description', N'注释', N'SCHEMA', N'用户',N'TABLE', N'表名'; --修改表注释: EXEC sp_updateextendedpropert.
MSSQL中定义注释 sp_addextendedproperty 时使用 user SCHEMA 这两个属性的区别
geekqian的博客
05-14 1093
MSSQL 创建表的时候需要为字段加注释, 通过搜索使用下列的方式: EXEC sp_addextendedproperty 'MS_Description',N'主键 ID','user','dbo','table','temp_special_vendor','COLUMN','id' 但在搜索过程中发现其中的 user 属性的位置还可以用 SCHEMA 替代, 于是查了下这两个属性的区别. 结论如下: 官方文档: https://docs.microsoft.com/en-us/sql/relati
SQL---存储过程---sp_addextendedproperty表字段加描述
weixin_30849591的博客
04-02 336
相信很多朋友对利用SQL创建表已经很熟悉了，但我们发现在创建表的同时不能像添加默认值或者主键一样为列加上说明信息，所以我们经常是创建表后再到表的可视化设计器中为列加上说明，这样操作起来就相当麻烦了，本篇我们主要讨论如何利用SQL在创建表时为列加上说明信息。我们先创建一个测试表：ifexists(select1fromsys.tableswhereobject_id=object_id(...
如何为数据库,表等对象添加描述信息
飞轩亭
02-06 6050
                                                              如何为数据库,表等对象添加描述信息--创建实例数据库testcreate database test --为test数据库添加拓展属性USE test;GOEXEC sys.sp_addextendedproperty @name = N
sql server 添加表注释、字段注释
热门推荐
StupidBird003的博客
03-21 1万+
--为字段添加注释 --格式如右：execute sp_addextendedproperty 'MS_Description','字段备注信息','user','dbo','table','字段所属的表名','column','添加注释的字段名'; execute sp_addextendedproperty 'MS_Description','add by liyc. 诊断类别码','use
[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]关键字 'SCHEMA' 附近有语法错误。 (156) [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]字符串 ';' 后的引号不完整。 (105)代码为CREATE TABLE rf_process_craft ( Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL, user_id INT NULL, user_name VARCHAR(50) NULL, work_order VARCHAR(50) NULL, work_order_no VARCHAR(50) NULL, project_no VARCHAR(100) NULL, project_name VARCHAR(200) NULL, project_category VARCHAR(100) NULL, job_order_no VARCHAR(100) NULL, part_drawing_no VARCHAR(100) NULL, part_name VARCHAR(200) NULL, material_type VARCHAR(100) NULL, material_spec VARCHAR(100) NULL, craft VARCHAR(200) NULL, order_quantity VARCHAR(50) NULL, order_date VARCHAR(50) NULL, delivery_date VARCHAR(50) NULL, production_priority VARCHAR(50) NULL, laser_hours VARCHAR(50) NULL, deburring_hours VARCHAR(50) NULL, bending_hours VARCHAR(50) NULL, weld_hours VARCHAR(50) NULL, burnish_hours VARCHAR(50) NULL, spray_plastic_hours VARCHAR(50) NULL, blanking_hours VARCHAR(50) NULL, cart_hours VARCHAR(50) NULL, mill_hours VARCHAR(50) NULL, clamp_hours VARCHAR(50) NULL, galvanize_hours VARCHAR(50) NULL, interior_hours VARCHAR(50) NULL, material_status VARCHAR(100) NULL, completed_quantity VARCHAR(50) NULL, pass_rate VARCHAR(50) NULL, required_quantity VARCHAR(50) NULL, single_part_craft_hours VARCHAR(50) NULL, daily_capacity VARCHAR(50) NULL, start_date VARCHAR(50) NULL, end_date VARCHAR(50) NULL, process_type VARCHAR(20) NULL, remark VARCHAR(500) NULL, create_by VARCHAR(50) NULL, create_time VARCHAR(50) NULL, update_by VARCHAR(50) NULL, update_time VARCHAR(50) NULL ); EXEC sp_addextendedproperty 'MS_Description', '工艺表', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft'; EXEC sp_addextendedproperty 'MS_Description', '主键id', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'Id'; EXEC sp_addextendedproperty 'MS_Description', '用户id', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'user_id'; EXEC sp_addextendedproperty 'MS_Description', '用户账号', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'user_name'; EXEC sp_addextendedproperty 'MS_Description', '工作令', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'work_order'; EXEC sp_addextendedproperty 'MS_Description', '工作令号', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'work_order_no'; EXEC sp_addextendedproperty 'MS_Description', '项目号', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'project_no'; EXEC sp_addextendedproperty 'MS_Description', '项目名', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'project_name'; EXEC sp_addextendedproperty 'MS_Description', '项目类别', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'project_category'; EXEC sp_addextendedproperty 'MS_Description', '工单号', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'job_order_no'; EXEC sp_addextendedproperty 'MS_Description', '零件图号', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'part_drawing_no'; EXEC sp_addextendedproperty 'MS_Description', '零件名称', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'part_name'; EXEC sp_addextendedproperty 'MS_Description', '材料种类', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'material_type'; EXEC sp_addextendedproperty 'MS_Description', '材料规格', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'material_spec'; EXEC sp_addextendedproperty 'MS_Description', '工艺', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'craft'; EXEC sp_addextendedproperty 'MS_Description', '订单数量', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'order_quantity'; EXEC sp_addextendedproperty 'MS_Description', '接单日期', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'order_date'; EXEC sp_addextendedproperty 'MS_Description', '交期', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'delivery_date'; EXEC sp_addextendedproperty 'MS_Description', '生产优先等级', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'production_priority'; EXEC sp_addextendedproperty 'MS_Description', '激光工时 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'laser_hours'; EXEC sp_addextendedproperty 'MS_Description', '去毛刺工时', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'deburring_hours'; EXEC sp_addextendedproperty 'MS_Description', '折弯工时', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'bending_hours'; EXEC sp_addextendedproperty 'MS_Description', '焊接工时', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'weld_hours'; EXEC sp_addextendedproperty 'MS_Description', '打磨工时', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'burnish_hours'; EXEC sp_addextendedproperty 'MS_Description', '喷塑工时', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'spray_plastic_hours'; EXEC sp_addextendedproperty 'MS_Description', '下料工时', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'blanking_hours'; EXEC sp_addextendedproperty 'MS_Description', '车工时', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'cart_hours'; EXEC sp_addextendedproperty 'MS_Description', '铣工时', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'mill_hours'; EXEC sp_addextendedproperty 'MS_Description', '钳工时', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'clamp_hours'; EXEC sp_addextendedproperty 'MS_Description', '镀锌工时', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'galvanize_hours'; EXEC sp_addextendedproperty 'MS_Description', '内装工时', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'interior_hours'; EXEC sp_addextendedproperty 'MS_Description', '备用字段', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'remark'; EXEC sp_addextendedproperty 'MS_Description', '创建者', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'create_by'; EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'create_time'; EXEC sp_addextendedproperty 'MS_Description', '更新者', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'update_by'; EXEC sp_addextendedproperty 'MS_Description', '更新时间', 'SCHEMA', 'dbo', 'TABLE', 'rf_process_craft', 'COLUMN', 'update_time'; 怎么解决
10-21
添加扩展属性时，SQL Server 使用系统存储过程 `sp_addextendedproperty`，其参数要求严格遵循格式：字符串值必须用单引号 `'` 包围、参数名正确指定，且保留字（如 `SCHEMA`）需大写。错误可能由以下原因导致： - *...
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[clue_focus]') AND type in (N'U')) DROP TABLE [dbo].[clue_focus]; CREATE TABLE [dbo].[clue_focus]( id BIGINT PRIMARY KEY, company_name NVARCHAR(200), open_date DATETIME, created_id NVARCHAR(20), created_name NVARCHAR(50), is_valid BIT, created_time DATETIME ); EXEC sp_addextendedproperty 'MS_Description', '线索关注表', 'SCHEMA', dbo, 'table', clue_focus, null, null; EXEC sp_addextendedproperty 'MS_Description', '租户号', 'SCHEMA', dbo, 'table', clue_focus, 'column', id; EXEC sp_addextendedproperty 'MS_Description', '企业名称', 'SCHEMA', dbo, 'table', clue_focus, 'column', company_name; EXEC sp_addextendedproperty 'MS_Description', '线索日期', 'SCHEMA', dbo, 'table', clue_focus, 'column', open_date; EXEC sp_addextendedproperty 'MS_Description', '创建人员编', 'SCHEMA', dbo, 'table', clue_focus, 'column', created_id; EXEC sp_addextendedproperty 'MS_Description', '创建人姓名', 'SCHEMA', dbo, 'table', clue_focus, 'column', created_name; EXEC sp_addextendedproperty 'MS_Description', '是否有效', 'SCHEMA', dbo, 'table', clue_focus, 'column', is_valid; EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', clue_focus, 'column', created_time;将这条SQL加上主键自增
09-10
另外，原脚本中使用`EXEC sp_addextendedproperty`为列添加描述，其中`id`列的描述是“租户号”，但改为自增后，可能需要确认描述是否需要调整，但用户没有提到修改描述，所以可能保持原样。 然后，需要检查是否有...
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[report_info]') AND type in (N'U')) DROP TABLE [dbo].[report_info]; CREATE TABLE [dbo].[report_info]( id BIGINT PRIMARY KEY, -- id company_name NVARCHAR(200), -- 企业名称 address NVARCHAR(100), -- 地址 province NVARCHAR(20), -- 省 city NVARCHAR(20), -- 市 district NVARCHAR(20), -- 区 start_date DATETIME(8), -- 日期 end_date DATETIME(8), -- 同上 visit_scene NVARCHAR(20), -- 进企场景 attendance_count INT(4), -- 到场人数 visit_activity NVARCHAR(20), -- 进企活动 invitation_time DATETIME(8), -- 邀约时间 invitation_description NVARCHAR(100), -- 邀约说明 invitation_channel NVARCHAR(50), -- 邀约渠道 invitation_dept_id NVARCHAR(20), -- 机构ID invitation_dept_name NVARCHAR(50), -- 机构名称 invitation_group_id NVARCHAR(20), -- 组ID initiator_id NVARCHAR(20), -- 发起方员编 initiator_name NVARCHAR(50), -- 姓名 recipient_id NVARCHAR(20), -- 接收方员编 recipient_name NVARCHAR(50), -- 接收方姓名 recipient_dept_id NVARCHAR(20), -- 接收方机构ID recipient_dept_name NVARCHAR(50), -- 接收方机构名称 recipient_group_id NVARCHAR(20), -- 接收方组ID recipient_group_name NVARCHAR(50), -- 接收方组名称 approver_id NVARCHAR(20), -- 审批人员编 approver_name NVARCHAR(50), -- 审批人姓名 submitter_id NVARCHAR(20), -- 提报人员编 submitter_name NVARCHAR(50), -- 提报人姓名 status NVARCHAR(20), -- 状态 creator_id NVARCHAR(20), -- 创建人员ID creator_name NVARCHAR(50), -- 创建人姓名 create_time DATETIME, -- 创建时间 update_time DATETIME, -- 更新时间 longitude NVARCHAR(20), -- 经度 latitude NVARCHAR(20), -- 纬度 invitation_group_name NVARCHAR(50), -- 邀约组名称 submitter_dept_id NVARCHAR(20), -- 提报机构ID submitter_dept_name NVARCHAR(50), -- 提报机构名称 submitter_group_id NVARCHAR(20), -- 提报组ID submitter_group_name NVARCHAR(50), -- 提报组名称 report_code NVARCHAR(50) -- 提报编码 ); EXEC sp_addextendedproperty 'MS_Description', '提报信息表', 'SCHEMA', dbo, 'table', report_info, null, null; EXEC sp_addextendedproperty 'MS_Description', '主键', 'SCHEMA', dbo, 'table', report_info, 'column', id; EXEC sp_addextendedproperty 'MS_Description', '企业名称', 'SCHEMA', dbo, 'table', report_info, 'column', enterprise_name; EXEC sp_addextendedproperty 'MS_Description', '所在地区', 'SCHEMA', dbo, 'table', report_info, 'column', address; EXEC sp_addextendedproperty 'MS_Description', '省', 'SCHEMA', dbo, 'table', report_info, 'column', province; EXEC sp_addextendedproperty 'MS_Description', '市', 'SCHEMA', dbo, 'table', report_info, 'column', city; EXEC sp_addextendedproperty 'MS_Description', '区', 'SCHEMA', dbo, 'table', report_info, 'column', district; EXEC sp_addextendedproperty 'MS_Description', '开始日期', 'SCHEMA', dbo, 'table', report_info, 'column', start_date; EXEC sp_addextendedproperty 'MS_Description', '结束日期', 'SCHEMA', dbo, 'table', report_info, 'column', end_date; EXEC sp_addextendedproperty 'MS_Description', '进企场景', 'SCHEMA', dbo, 'table', report_info, 'column', visit_scene; EXEC sp_addextendedproperty 'MS_Description', '到场人数', 'SCHEMA', dbo, 'table', report_info, 'column', attendance_count; EXEC sp_addextendedproperty 'MS_Description', '进企活动', 'SCHEMA', dbo, 'table', report_info, 'column', visit_activity; EXEC sp_addextendedproperty 'MS_Description', '邀约时间', 'SCHEMA', dbo, 'table', report_info, 'column', invitation_time; EXEC sp_addextendedproperty 'MS_Description', '邀约说明', 'SCHEMA', dbo, 'table', report_info, 'column', invitation_description; EXEC sp_addextendedproperty 'MS_Description', '邀约发起渠道', 'SCHEMA', dbo, 'table', report_info, 'column', invitation_channel; EXEC sp_addextendedproperty 'MS_Description', '邀约发起机构', 'SCHEMA', dbo, 'table', report_info, 'column', invitation_dept_id; EXEC sp_addextendedproperty 'MS_Description', '邀约发起机构名称', 'SCHEMA', dbo, 'table', report_info, 'column', invitation_dept_name; EXEC sp_addextendedproperty 'MS_Description', '邀约发起组编号', 'SCHEMA', dbo, 'table', report_info, 'column', invitation_group_id; EXEC sp_addextendedproperty 'MS_Description', '邀约发起方员编', 'SCHEMA', dbo, 'table', report_info, 'column', initiator_id; EXEC sp_addextendedproperty 'MS_Description', '邀约发起方姓名', 'SCHEMA', dbo, 'table', report_info, 'column', initiator_name; EXEC sp_addextendedproperty 'MS_Description', '接收方员编', 'SCHEMA', dbo, 'table', report_info, 'column', recipient_id; EXEC sp_addextendedproperty 'MS_Description', '接收方姓名', 'SCHEMA', dbo, 'table', report_info, 'column', recipient_name; EXEC sp_addextendedproperty 'MS_Description', '接收方机构id', 'SCHEMA', dbo, 'table', report_info, 'column', recipient_dept_id; EXEC sp_addextendedproperty 'MS_Description', '接收方机构名称', 'SCHEMA', dbo, 'table', report_info, 'column', recipient_dept_name; EXEC sp_addextendedproperty 'MS_Description', '接收方支行id', 'SCHEMA', dbo, 'table', report_info, 'column', recipient_group_id; EXEC sp_addextendedproperty 'MS_Description', '接收方支行名称', 'SCHEMA', dbo, 'table', report_info, 'column', recipient_group_name; EXEC sp_addextendedproperty 'MS_Description', '审批人员编', 'SCHEMA', dbo, 'table', report_info, 'column', approver_id; EXEC sp_addextendedproperty 'MS_Description', '审批人姓名', 'SCHEMA', dbo, 'table', report_info, 'column', approver_name; EXEC sp_addextendedproperty 'MS_Description', '提报人员编', 'SCHEMA', dbo, 'table', report_info, 'column', submitter_id; EXEC sp_addextendedproperty 'MS_Description', '提报人姓名', 'SCHEMA', dbo, 'table', report_info, 'column', submitter_name; EXEC sp_addextendedproperty 'MS_Description', '状态', 'SCHEMA', dbo, 'table', report_info, 'column', status; EXEC sp_addextendedproperty 'MS_Description', '创建人员编', 'SCHEMA', dbo, 'table', report_info, 'column', creator_id; EXEC sp_addextendedproperty 'MS_Description', '创建人姓名', 'SCHEMA', dbo, 'table', report_info, 'column', creator_name; EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', report_info, 'column', create_time; EXEC sp_addextendedproperty 'MS_Description', '更新时间', 'SCHEMA', dbo, 'table', report_info, 'column', update_time; EXEC sp_addextendedproperty 'MS_Description', '经度', 'SCHEMA', dbo, 'table', report_info, 'column', longitude; EXEC sp_addextendedproperty 'MS_Description', '纬度', 'SCHEMA', dbo, 'table', report_info, 'column', latitude; EXEC sp_addextendedproperty 'MS_Description', '发起方组号', 'SCHEMA', dbo, 'table', report_info, 'column', invitation_group_name; EXEC sp_addextendedproperty 'MS_Description', '提报人部门编号', 'SCHEMA', dbo, 'table', report_info, 'column', submitter_dept_id; EXEC sp_addextendedproperty 'MS_Description', '提报人部门名称', 'SCHEMA', dbo, 'table', report_info, 'column', submitter_dept_name; EXEC sp_addextendedproperty 'MS_Description', '提报人组号', 'SCHEMA', dbo, 'table', report_info, 'column', submitter_group_id; EXEC sp_addextendedproperty 'MS_Description', '提报人组名称', 'SCHEMA', dbo, 'table', report_info, 'column', submitter_group_name; EXEC sp_addextendedproperty 'MS_Description', '提报码', 'SCHEMA', dbo, 'table', report_info, 'column', report_code; IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[report_flow]') AND type in (N'U')) DROP TABLE [dbo].[report_flow]; CREATE TABLE [dbo].[report_flow]( id BIGINT, clue_id BIGINT, report_id BIGINT, content NVARCHAR(50), created_id NVARCHAR(20), created_name NVARCHAR(50), created_time DATETIME, node NVARCHAR(20) ); EXEC sp_addextendedproperty 'MS_Description', '流水记录表', 'SCHEMA', dbo, 'table', report_flow, null, null; EXEC sp_addextendedproperty 'MS_Description', '租户号', 'SCHEMA', dbo, 'table', report_flow, 'column', id; EXEC sp_addextendedproperty 'MS_Description', '线索id', 'SCHEMA', dbo, 'table', report_flow, 'column', clue_id; EXEC sp_addextendedproperty 'MS_Description', '提报id', 'SCHEMA', dbo, 'table', report_flow, 'column', report_id; EXEC sp_addextendedproperty 'MS_Description', '记录', 'SCHEMA', dbo, 'table', report_flow, 'column', content; EXEC sp_addextendedproperty 'MS_Description', '创建人员编', 'SCHEMA', dbo, 'table', report_flow, 'column', created_id; EXEC sp_addextendedproperty 'MS_Description', '创建人姓名', 'SCHEMA', dbo, 'table', report_flow, 'column', created_name; EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', report_flow, 'column', created_time; EXEC sp_addextendedproperty 'MS_Description', '节点名称', 'SCHEMA', dbo, 'table', report_flow, 'column', node; IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[clue_focus]') AND type in (N'U')) DROP TABLE [dbo].[clue_focus]; CREATE TABLE [dbo].[clue_focus]( id BIGINT, company_name NVARCHAR(200), open_date DATETIME(8), created_id NVARCHAR(20), created_name NVARCHAR(50), is_valid BIT(4), created_time DATETIME(8) ); EXEC sp_addextendedproperty 'MS_Description', '线索关注表', 'SCHEMA', dbo, 'table', clue_focus, null, null; EXEC sp_addextendedproperty 'MS_Description', '租户号', 'SCHEMA', dbo, 'table', clue_focus, 'column', id; EXEC sp_addextendedproperty 'MS_Description', '企业名称', 'SCHEMA', dbo, 'table', clue_focus, 'column', enterprise_name; EXEC sp_addextendedproperty 'MS_Description', '线索日期', 'SCHEMA', dbo, 'table', clue_focus, 'column', open_date; EXEC sp_addextendedproperty 'MS_Description', '创建人员编', 'SCHEMA', dbo, 'table', clue_focus, 'column', created_id; EXEC sp_addextendedproperty 'MS_Description', '创建人姓名', 'SCHEMA', dbo, 'table', clue_focus, 'column', created_name; EXEC sp_addextendedproperty 'MS_Description', '是否有效', 'SCHEMA', dbo, 'table', clue_focus, 'column', is_valid; EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', clue_focus, 'column', created_time; IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mentor_record_flow]') AND type in (N'U')) DROP TABLE [dbo].[mentor_record_flow]; CREATE TABLE [dbo].[mentor_record_flow]( id BIGINT, action NVARCHAR(100), request NVARCHAR(2000), created_id NVARCHAR(20), created_time DATETIME(8), response NVARCHAR(2000) ); EXEC sp_addextendedproperty 'MS_Description', '带教动作流水表', 'SCHEMA', dbo, 'table', mentor_record_flow, null, null; EXEC sp_addextendedproperty 'MS_Description', '主键', 'SCHEMA', dbo, 'table', mentor_record_flow, 'column', id; EXEC sp_addextendedproperty 'MS_Description', '动作', 'SCHEMA', dbo, 'table', mentor_record_flow, 'column', action; EXEC sp_addextendedproperty 'MS_Description', '记录', 'SCHEMA', dbo, 'table', mentor_record_flow, 'column', request; EXEC sp_addextendedproperty 'MS_Description', '创建人员编', 'SCHEMA', dbo, 'table', mentor_record_flow, 'column', created_id; EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', mentor_record_flow, 'column', created_time; EXEC sp_addextendedproperty 'MS_Description', '', 'SCHEMA', dbo, 'table', mentor_record_flow, 'column', response; 这个SQL有问题码
09-09
这会导致 SQL Server 无法为 `company_name` 字段添加注释（因为 `enterprise_name` 字段不存在），最终该字段无描述信息。 #### 2. `clue_focus`表的`company_name`字段 - **问题**：表中定义了字段 `company_...
use smmp_987_imp go create table dbo.imp_target_refer_dept ( refer_id bigint identity primary key, target_key varchar(20), time_type varchar(2), refer_year varchar(10), refer_month varchar(10), refer_goal varchar(20), refer_danger varchar(20), refer_max varchar(20), refer_min varchar(20), refer_median varchar(20), refer_avg varchar(20), refer_float_rate varchar(20), refer_float_symbol varchar(20), refer_eval varchar(1000), create_by varchar(50), create_time datetime, dept_id bigint ) go exec sp_addextendedproperty 'MS_Description', '指标参考表', 'SCHEMA', 'dbo', 'TABLE', 'imp_target_refer_dept' go exec sp_addextendedproperty 'MS_Description', '自增ID', 'SCHEMA', 'dbo', 'TABLE', 'imp_target_refer_dept', 'COLUMN', 'refer_id' go exec sp_addextendedproperty 'MS_Description', '指标key', 'SCHEMA', 'dbo', 'TABLE', 'imp_target_refer_dept', 'COLUMN', 'target_key' go exec sp_addextendedproperty 'MS_Description', '参考时间类型', 'SCHEMA', 'dbo', 'TABLE', 'imp_target_refer_dept', 'COLUMN', 'time_type' go exec sp_addextendedproperty 'MS_Description', '参考年份', 'SCHEMA', 'dbo', 'TABLE', 'imp_target_refer_dept', 'COLUMN', 'refer_year' go exec sp_addextendedproperty 'MS_Description', '参考月份', 'SCHEMA', 'dbo', 'TABLE', 'imp_target_refer_dept', 'COLUMN', 'refer_month' go exec sp_addextendedproperty 'MS_Description', '目标值', 'SCHEMA', 'dbo', 'TABLE', 'imp_target_refer_dept', 'COLUMN', 'refer_goal' go exec sp_addextendedproperty 'MS_Description', '危险值', 'SCHEMA', 'dbo', 'TABLE', 'imp_target_refer_dept', 'COLUMN', 'refer_danger' go exec sp_addextendedproperty 'MS_Description', '最大值', 'SCHEMA', 'dbo', 'TABLE', 'imp_target_refer_dept', 'COLUMN', 'refer_max' go exec sp_addextendedproperty 'MS_Description', '最小值', 'SCHEMA', 'dbo', 'TABLE', 'imp_target_refer_dept', 'COLUMN', 'refer_min' go exec sp_addextendedproperty 'MS_Description', '中位值', 'SCHEMA', 'dbo', 'TABLE', 'imp_target_refer_dept', 'COLUMN', 'refer_median' go exec sp_addextendedproperty 'MS_Description', '平均值', 'SCHEMA', 'dbo', 'TABLE', 'imp_target_refer_dept', 'COLUMN', 'refer_avg' go exec sp_addextendedproperty 'MS_Description', '目标浮动率', 'SCHEMA', 'dbo', 'TABLE', 'imp_target_refer_dept', 'COLUMN', 'refer_float_rate' go exec sp_addextendedproperty 'MS_Description', '目标浮动率符号', 'SCHEMA', 'dbo', 'TABLE', 'imp_target_refer_dept', 'COLUMN', 'refer_float_symbol' go exec sp_addextendedproperty 'MS_Description', '评估方式', 'SCHEMA', 'dbo', 'TABLE', 'imp_target_refer_dept', 'COLUMN', 'refer_eval' go exec sp_addextendedproperty 'MS_Description', '创建人', 'SCHEMA', 'dbo', 'TABLE', 'imp_target_refer_dept', 'COLUMN', 'create_by' go exec sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', 'dbo', 'TABLE', 'imp_target_refer_dept', 'COLUMN', 'create_time' go exec sp_addextendedproperty 'MS_Description', '科室id', 'SCHEMA', 'dbo', 'TABLE', 'imp_target_refer_dept', 'COLUMN', 'dept_id' go 这个表新增一个科室名称的字段
12-05
-- 为新增字段添加描述 EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'科室名称', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'TABLE', @level1name = 'imp_...
代码解释 ```sql EXEC sp_addextendedproperty @name = N'MS_Description', @value = '学生选课记录表', @level0type = 'SCHEMA', @level0name = 'dbo', @level1type = 'TABLE', @level1name = 'SC'; EXEC sp_addextendedproperty @name = N'MS_Description', @value = '学生学号', @level0type = 'SCHEMA', @level0name = 'dbo', @level1type = 'TABLE', @level1name = 'SC', @level2type = 'COLUMN', @level2name = 'Sno'; EXEC sp_addextendedproperty @name = N'MS_Description', @value = '课程编号', @level0type = 'SCHEMA', @level0name = 'dbo', @level1type = 'TABLE', @level1name = 'SC', @level2type = 'COLUMN', @level2name = 'Cno'; EXEC sp_addextendedproperty @name = N'MS_Description', @value = '考试成绩：0~100，可为空', @level0type = 'SCHEMA', @level0name = 'dbo', @level1type = 'TABLE', @level1name = 'SC', @level2type = 'COLUMN', @level2name = 'Grade'; EXEC sp_addextendedproperty @name = N'MS_Description', @value = '选课学期，如2023-2024-1', @level0type = 'SCHEMA', @level0name = 'dbo', @level1type = 'TABLE', @level1name = 'SC', @level2type = 'COLUMN', @level2name = 'SelectSemester'; EXEC sp_addextendedproperty @name = N'MS_Description', @value = '选课状态', @level0type = 'SCHEMA', @level0name = 'dbo', @level1type = 'TABLE', @level1name = 'SC', @level2type = 'COLUMN', @level2name = 'SelectStatus'; GO ```
最新发布
12-12
在这段代码中，以下这一组 `EXEC sp_addextendedproperty` 语句的主要功能是：**为数据库对象（表和列）添加描述性注释（元数据），以增强数据库的可读性和可维护性**。这些注释不会影响数据库的运行逻辑，但对开发...
如何利用SQL创建与修改列的说明信息(备注信息)详解
路在何方
11-08 5298
相信很多朋友对利用SQL创建表已经很熟悉了，但我们发现在创建表的同时不能像添加默认值或者主键一样为列加上说明信息，所以我们经常是创建表后再到表的可视化设计器中为列加上说明，这样操作起来就相当麻烦了，本篇我们主要讨论如何利用SQL在创建表时为列加上说明信息。 我们先创建一个测试表： if exists(select 1 from sys.tables where object_id=ob
sqlserver 给表和字段添加注释
m0_55788506的博客
07-03 1093
sqlserver的表注释不同于其他数据库，因此，新手常常会弄错
sp_addextendedproperty
weixin_33749242的博客
06-26 200
这好像是个很复杂的函数，sql帮助文档看了半天看不懂，我就会用它填写注释 - - execute sp_addextendedproperty 'MS_Description', '接收时间', 'user', 'dbo', 'table', 'BS_Track_Info', 'column', 'RecieveTime' go 说明： 'MS_Des...
视图中的 sp_addextendedproperty
改吧
10-11 1036
 --參考--表及字段描述信息处理示例--创建表create table 表(a1 varchar(10),a2 char(2))--为表添加描述信息EXECUTE sp_addextendedproperty NMS_Description, 人员信息表, Nuser, Ndbo, Ntable, N表, NULL, NULL--为
sql中 sp_addextendedproperty详解
秀出自己的风采
12-21 1万+
sp_addextendedproperty 将新扩展属性添加到数据库对象中。如果属性已经存在，则过程失败。 语法 sp_addextendedproperty     [ @name = ] { 'property_name' }     [ , [ @value = ] { 'value' }         [ , [ @level0type = ] { 'level0_
【SqlServer】使用sp_addextendedproperty为表的列添加说明
GreAmbWang的博客
06-06 1万+
一般，我们都是在界面对列添加说明 如果我们想用sql语句添加列说明呢，我们可以用系统自带的存储过程sp_addextendedproperty 如果我们想了解sp_addextendedproperty的基本使用 可以先在表的一列添加说明，比如我先在Student表的ID上添加了说明“这里是ID” 然后右键表弹出右键菜单，选择编写脚本为》CREATE 到》新建查询编辑器窗口 在最...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值