SQL 创建字段说明并查询

yuemingfuyueming

于 2017-07-11 15:38:21 发布

阅读量659

点赞数

分类专栏： sql

sql 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一个简单的SQL脚本，用于创建一个名为'student'的表，包含两个字段：'id'和'name'，并为这两个字段添加了描述。'id'字段用于存储学生的学号，而'name'字段用于存储学生的姓名。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create table student
(
id int not NULL,
name varchar(10) not null
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'student', @level2type=N'COLUMN',@level2name=N'id'
GO
EXEC sys.sp_updateextendedproperty 
@name=N'MS_Description', 
@value=N'姓名' , 
@level0type=N'SCHEMA',
@level0name=N'dbo', 
@level1type=N'TABLE',
@level1name=N'student', 
@level2type=N'COLUMN',
@level2name=N'name'
GO

SELECT t.[name] AS 表名,c.[name] AS 字段名,cast(ep.[value]   
as varchar(100)) AS [字段说明]  
FROM sys.tables AS t  
INNER JOIN sys.columns   
AS c ON t.object_id = c.object_id  
 LEFT JOIN sys.extended_properties AS ep   
ON ep.major_id = c.object_id AND ep.minor_id = c.column_id WHERE ep.class =1   
AND t.name='student'