如何利用SQL建立與修改列的說明資訊(備注資訊)詳解

 相信很多朋友對利用SQL建立表已經很熟悉了，但我們發現在建立表的同時不能像添加預設值或者主鍵一樣為列加上說明資訊，是以我們經常是建立表後再到表的可視化設計器中為列加上說明，這樣操作起來就相當麻煩了，本篇我們主要讨論如何利用SQL在建立表時為列加上說明資訊。

我們先建立一個測試表：

if exists(select 1 from sys.tables where object_id=object_id('test'))

begin

drop table test

end

create table test

(

col1 varchar(50),

col2 varchar(50)

)

這個我們已經很熟悉了，那麼怎麼為列col1及col2加上說明資訊呢？

這就要用到系統存儲過程sp_addextendedproperty。

在添加之前我們先看一下sp_addextendedproperty的文法：

sp_addextendedproperty

  [ @name = ] { 'property_name' }

  [ , [ @value = ] { 'value' } 

  [ , [ @level0type = ] { 'level0_object_type' } 

  , [ @level0name = ] { 'level0_object_name' } 

  [ , [ @level1type = ] { 'level1_object_type' } 

  , [ @level1name = ] { 'level1_object_name' } 

  [ , [ @level2type = ] { 'level2_object_type' } 

  , [ @level2name = ] { 'level2_object_name' } 

  ] 

  ]

  ] 

  ] 

該存儲過程一共有8個參數，估計初學者一看就暈了，不要着急，我們可以通過執行個體來了解，下面我們先利用sp_addextendedproperty為col1列加上說明：

execute sp_addextendedproperty N'MS_Description',N'這是測試列1',N'SCHEMA',N'dbo',N'table',N'test',N'column',N'col1'

上面就是為列col1加上說明的SQL指令，來了解一下:

雖然sp_addextendedproperty有8個參數，但我們可以把這個8個參數了解為4對，其實看參數名我們也可以猜出大概了:

@name與@value為一對

@level0type與@level0name為一對

@level1type與@level1name為一對

@level2type與@level2name為一對

那麼這4對參數分别代表什麼呢？

1,@name與@value

@name：指定我們是要為列添加什麼資訊，比如我們要為列添加擴充資訊，那麼@name就等于'Caption',本篇是要為列添加說明資訊，是以@name等于'MS_Description'

@value：指定與@name關聯的值，本篇也就是列的具體說明。

2,@level0type與@level0name

@level0type：指定我們要修改的列的表所于那個資料庫架構，是以它等于'SCHEMA'，有些網上教程中會說它也可以等于'user'，但在sql server的未來版本中，将删除'user',是以推薦大家用'SCHEMA'

@level0name：指定我們要修改的表所在架構的名稱

3,@level1type與@level1name

@level1type：指明我們要修改的列所屬對象是表，還是視圖等。本篇是修改表中的列，是以為'table',

@@level1name: 指明要修改的列所屬表的名稱

4,@level2type與@level2name

@level2type：指明我們要修改的對象是列，還是主鍵，還是限制等。本篇修改的是列,是以為'column'

@level2name：指明要修改列的列名

到此，我們應該了解sp_addextendedproperty中各參數的意思了，完整的SQL指令如下：

if exists(select 1 from sys.tables where object_id=object_id('test'))

begin

drop table test

end

create table test

(

col1 varchar(50),

col2 varchar(50)

)

execute sp_addextendedproperty N'MS_Description',N'這是測試列1',N'SCHEMA',N'dbo',N'table',N'test',N'column',N'col1'

execute sp_addextendedproperty N'MS_Description',N'這是測試列2',N'SCHEMA',N'dbo',N'table',N'test',N'column',N'col2'

執行指令後通過如下查詢語句來驗證我們的結果：

select B.Name,A.value from sys.extended_properties A 

inner join sys.columns B on A.major_id=B.object_id 

and A.minor_id=B.column_id

where A.major_id=object_id('test')

關于如何利用SQL語句查詢指定表的列說明資訊，請參看本站：

利用SQL語句查詢出指定表的所有擴充屬性(列說明)

執行，傳回的結果如下：

Name value

col1 這是測試列1

col2 這是測試列2

建立表時同時為表中的列添加說明資訊我們已經完成了，那麼，我們要如何利用SQL來修改列的說明資訊呢？利用系統存儲過程sp_updateextendedproperty，它的用法和sp_addextendedproperty一樣，就不多加說明了，修改示例如下：

execute sp_updateextendedproperty N'MS_Description',N'這是修改後的測試列1',N'SCHEMA',N'dbo',N'table',N'test',N'column',N'col1'

execute sp_updateextendedproperty N'MS_Description',N'這是修改後的測試列2',N'SCHEMA',N'dbo',N'table',N'test',N'column',N'col2'

同樣利用上面的SQL語句查詢修改後的結果：

Name value

col1 這是修改後的測試列1

col2 這是修改後的測試列2

本文來源于: http://www.lmwlove.com/ac/ID666