sqlserver把列说明转成一列数据

sqlserver中可以对库说明、表说明、表的列说明，这些说明起到注释的作用。

有天同事问到如何把列说明变成一个字段转成表的数据，就不用那么麻烦每次去改表了。

在数据库中可以这样查

先在shiyan1数据库中

EXEC [shiyan1].sys.sp_addextendedproperty @name=N'1', @value=N'库说明无意义' 
GO

CREATE TABLE [dbo].[MyTest](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](100) NULL,
[times] [datetime] NULL,
 CONSTRAINT [PK__MyTest__3213E83F3B0BADF5] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


GO


SET ANSI_PADDING OFF
GO


ALTER TABLE [dbo].[MyTest] ADD  CONSTRAINT [DF_MyTest_times]  DEFAULT (getdate()) FOR [times]
GO


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID标识' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTest', @level2type=N'COLUMN',@level2name=N'id'
GO


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTest', @level2type=N'COLUMN',@level2name=N'name'
GO


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTest', @level2type=N'COLUMN',@level2name=N'times'
GO


EXEC sys.sp_addextendedproperty @name=N'1', @value=N'表说明，测试无意义' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTest'
GO

再查看

根据class_desc 和major_id,很明显有库说明、表说明和不同表列说明 。获得需要的就把库说明和表说明建个视图去掉就行了。

再根据sys.syscolumns和sys.sysobjects系统视图就能查出来

use [shiyan1]
SELECT   v.name, v.colorder, v.length,v.tableName,ISNULL(p.value, v.name) AS Descript
FROM      sys.extended_properties AS p INNER JOIN
(SELECT   col.name, col.colorder, col.length, obj.name AS tableName, obj.id
FROM      sys.syscolumns AS col INNER JOIN
                sys.sysobjects AS obj ON col.id = obj.id
WHERE   ((obj.type = 'u') OR
                (obj.type = 'V') )and obj.name='MyTest') AS v ON p.major_id = v.id AND p.minor_id = v.colorder

得到想要的查询，可以把这查询创建成相应的视图，每次调用就会方便很多。