6

Capturing Extended Properties on Drop Table | The Lone DBA

 3 years ago
source link: https://thelonedba.wordpress.com/2021/03/06/capturing-extended-properties-on-drop-table/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

TL;DR – Nope.

Extended Properties

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

The output:

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.

Conclusion

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

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK