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.
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'
