TL;DR – Nope.
Custom extended properties can be a useful tool when creating a self-documenting database. However, in an environment where tables are routinely dropped and recreated by software/systems not within your control, it may be desirable to have these stored so they can be repopulated when the table is recreated.
The obvious way to capture this data for this purpose would be to run a query in a DROP_TABLE trigger and store the output in a separate table for (re-)use later (eg on a create table trigger to repopulate the values as they were when the table is recreated).
USE Testing; DROP TRIGGER IF EXISTS DropTableGetVars ON DATABASE; IF OBJECT_ID ('TestDrop', 'U') IS NOT NULL EXEC ('DROP TABLE TestDrop'); GO CREATE TABLE TestDrop (i INT); GO EXEC sp_addextendedproperty @level0type = 'SCHEMA', @level0name = 'dbo', @level1type = 'TABLE', @level1name = 'TestDrop', @name = 'Purpose', @value = 'Table for testing drop functionality'; GO
These first few lines to drop anything that we might be about to use, and to create a test table and add a custom extended property.
Now, a query to check the extended property is there by querying the sys.extended_properties view:
SELECT s.name, t.name, ep.name, ep.value FROM sys.extended_properties AS ep INNER JOIN sys.tables AS t ON ep.major_id = t.object_id INNER JOIN sys.schemas AS s ON s.schema_id = t.schema_id;
And the results:
Drop Table Trigger
Now, a quick trigger to capture (well, show, in this case) the EPs when dropping the table:
CREATE TRIGGER DropTableGetVars ON DATABASE FOR DROP_TABLE AS BEGIN PRINT 'Drop table trigger'; SELECT s.name, t.name, ep.name, ep.value FROM sys.extended_properties AS ep INNER JOIN sys.tables AS t ON ep.major_id = t.object_id INNER JOIN sys.schemas AS s ON s.schema_id = t.schema_id; END; GO
A very simple trigger – print a message, and run that select statement. The idea being (later) to enhance that functionality by inserting the data into another table.
Anyway, let’s test it:
DROP TABLE TestDrop; GO
Drop table trigger (0 rows affected) Completion time: 2021-03-06T12:18:39.2286808+00:00
And the results of the SELECT:
Nothing there. Bother. It’s already gone before we get here.
This is the wrong approach for what I need to do – the extended properties are apparently deleted before the drop table trigger is fired. Which is a bore, and means I have to do one (or more) of:
- write triggers against the extended properties object itself (no)
- rewrite the extended properties procedures (no)
- wrap the extended properties maintenance SPs (depends on people using your maintenance SPs rather than the default / standard ones)
- write a routine to periodically poll sys.extended_properties and store the results elsewhere (probably the most reliable, assuming these don’t change value regularly, but a faff.)