วิธีการใช้คุณสมบัติส่วนขยาย (Extended Properties) ใน MS SQL Server Management Studio (SSMS)

Extended Properties เป็นจุดเด่นของ SSMS สามารถนำมาประยุกต์ใช้ได้หลายอย่างเช่น

• Specify a caption for a table, view, or column.
• Specify a display mask for a column.
• Display a format of a column, define edit mask for a date column, define number of decimals, etc.
• Specify formatting rules for displaying the data in a column.
• Describe a specific database objects for all users.

ที่เคยใช้คือใส่ข้อมูลอธิบายใน Description ของ แต่ละ Table Column ดูได้จากลิ้งค์นี้
นอกจากนั้นยังสามารถนำมาใช้ได้กับอย่างอื่นได้อีกด้วย

Object ที่ใช้ Extended Properties ได้

• Database
• Stored Procedures
• User-defined Functions
• Table
• Table Column
• Table Index
• Views
• Rules
• Triggers
• Constraints 

ในที่นี้จะแสดงตัวอย่างดังนี้

 
1.วิธีการสร้าง/ปรับปรุง/ลบ คุณสมบัติส่วนขยาย Add, Update and Drop Extended Properties.

2.วิธีการดึงคุณสมบัติส่วนขยายที่สร้างไว้มาแสดง

3.วิธีการใช้ฟังก์ชัน FN_LISTEXTENDEDPROPERTY()


แนะนำวิธีสร้าง Extended Properties ใน Database ไว้ในที่นี้ 2 แบบ
แบบที่ 1. คลิกขวาที่ Database เลือก Properties แล้วเลือก Extended Properties
               แล้วป้อนค่าที่ต้องการในช่องใต้ Name, Value
               (อย่าลืมว่าในช่อง Name  จะต้องใส่ชื่อที่ไม่ซ้ำกับ ชื่อเดิมที่มีอยู่แล้ว)
               เสร็จแล้วคลิกปุ่ม OK เพื่อบันทึกข้อมูลที่ป้อนไว้

                เมื่อสร้างเสร็จแล้ว ลอง Query เรียกดูด้วยคำสั่ง

SELECT * FROM sys.extended_properties

   
แบบที่ 2. เรียกใช้ Stored Procedure

EXEC   sp_addextendedproperty 'MS_Description', 'Test DB Description'

--เรียกดู Extended Properties เฉพาะ Level ของ Database เท่านั้น
SELECT
   DB_NAME() AS DbName,
   p.name AS ExtendedPropertyName,
   p.value AS ExtendedPropertyValue
FROM
   sys.extended_properties AS p
WHERE
   p.major_id=0 
   AND p.minor_id=0 
   AND p.class=0
ORDER BY
   [Name] ASC

--เรียกดู Extended Properties ทั้งหมดใน Database นั้น
SELECT * FROM sys.extended_properties

--เรียกดู Database
SELECT DB_NAME(database_id) AS [DB Name], database_id  FROM sys.databases
--เรียกดู Schema
select * from sys.schemas
--เรียกดู Table/Column
select * from INFORMATION_SCHEMA.COLUMNS
select *  from sys.tables  


--เรียกดู Column เฉพาะที่มี Description
USE YourDatabase
SELECT
 [Table Name] = i_s.TABLE_NAME,
 [Column Name] = i_s.COLUMN_NAME,
 [Data Type] = i_s.DATA_TYPE,
 [Description] = s.value
FROM
 INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN
 sys.extended_properties s
ON
 s.major_id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
 AND s.minor_id = i_s.ORDINAL_POSITION
 AND s.name = 'MS_Description'
WHERE
 OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0
 --AND i_s.TABLE_NAME = 'YourTableName'
ORDER BY
 i_s.TABLE_NAME, i_s.ORDINAL_POSITION

--เรียกดูทุก Object ที่มี Description
SELECT S.name as [Schema Name], O.name AS [Object Name], ep.name, ep.value AS [Extended property]
FROM sys.extended_properties EP
LEFT JOIN sys.all_objects O ON ep.major_id = O.object_id 
LEFT JOIN sys.schemas S on O.schema_id = S.schema_id
LEFT JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id

--เรียกดู Description โดยใช้ Function ชื่อ fn_listextendedproperty
SELECT * FROM ::fn_listextendedproperty ('MS_Description2','Schema','dbo','Table','PageContent','Column','page') 
SELECT * FROM fn_listextendedproperty ('MS_Description2','Schema','dbo','Table','PageContent','Column','page') 
SELECT * FROM fn_listextendedproperty (NULL,'Schema','dbo','Table','PageContent','Column','page') 
SELECT * FROM fn_listextendedproperty (NULL,'Schema','dbo','Table','PageContent','Column',NULL)
SELECT * FROM fn_listextendedproperty (NULL,NULL,NULL,NULL,default,NULL,NULL)
SELECT * FROM fn_listextendedproperty (NULL,NULL,default,NULL,default,NULL,default)

--Add Extended Property (Database Level)

EXEC   sp_addextendedproperty 'MS_Description', 'Test DB Description'

--Update Extended Property (Database Level)

EXEC   sp_updateextendedproperty 'MS_Description', 'Test DB Description2'

--Drop Extended Property (Database Level)

EXEC   sp_dropextendedproperty  'MS_Description'

--Add Table / View Extended Properties

EXEC   sp_addextendedproperty 'MS_Description', 'Test Table Description','Schema', dbo, 'table', 'YourTableName'
EXEC   sp_addextendedproperty 'MS_Description2', 'Test View Description2','Schema', dbo, 'View', 'YourViewName'

--Add Column Extended Properties

EXEC   sp_addextendedproperty 'MS_Description', 'View Column Description','Schema', dbo, 'View', 'YourViewName', 'Column', YourColumnName
EXEC   sp_addextendedproperty 'MS_Description', 'Your Description', 'user', dbo, 'table', 'YourTableName', 'column', YourColumnName
EXEC   sp_addextendedproperty 'MS_Description3', 'Your Description', 'user', dbo, 'table', 'YourTableName', 'column', YourColumnName
EXEC   sp_addextendedproperty 'MS_Description4', 'Your Description', 'Schema', dbo, 'table', 'YourTableName', 'column', YourColumnName

--Add Stored Procedure Extended Properties

EXEC   sp_addextendedproperty 'YourProcedureExtenedPropertyName', 'Test Stored Procedure Description','Schema', dbo, 'Procedure', 'YourStoredProcedureName'

--Add Trigger Extended Properties

EXEC   sp_addextendedproperty 'YourTriggerExtenedPropertyName', 'Test Trigger Description','Schema', dbo, 'Table', 'YourTableName','Trigger','YourTriggerName'

วิธี Select Description ของ Table ใน SQL Server มา

SSMS ในหน้า Design > Table > Column > ช่อง Description สามารถใส่คำอธิบายที่เกี่ยวข้องกับคอลัมน์นั้นได้

USE [YourDataBaseName]
SELECT
 [Table Name] = i_s.TABLE_NAME,
 [Column Name] = i_s.COLUMN_NAME,
 [Description] = s.value
FROM
 INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN
 sys.extended_properties s
ON
 s.major_id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
 AND s.minor_id = i_s.ORDINAL_POSITION
 AND s.name = 'MS_Description'
WHERE
 OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0
 --AND i_s.TABLE_NAME = 'YourTableName'
ORDER BY
 i_s.TABLE_NAME, i_s.ORDINAL_POSITION