Using SQL Extended Properties on a table.

This is a continuation of a previous post where I put a version number on the whole database.

To do the same thing on a particular table take a look at this sample:

USE sampledb

EXEC sys.sp_dropextendedproperty
@name = N'Version'
, @level0type = N'SCHEMA', @level0name = dbo
, @level1type = N'TABLE', @level1name = Table_1
;
GO

EXEC sys.sp_addextendedproperty
@name = N'Version'
, @value = N'test 01.02'
, @level0type = N'SCHEMA', @level0name = dbo
, @level1type = N'TABLE', @level1name = Table_1
;
GO

EXEC sys.sp_updateextendedproperty
@name = N'Version'
, @value = N'test 01.03'
, @level0type = N'SCHEMA', @level0name = dbo
, @level1type = N'TABLE', @level1name = Table_1
;
GO

The first time the above script is run, of course, the sp_dropextendedproperty will give an error of course.

The trick (in my case) was to get the SCHEMA name right – the rest should be a piece of cake!  Otherwise the MS documentation for the sproc’s is pretty clear – they just use a schema name that wasn’t immediately clear to me.

Advertisements
Posted in SQL