Extended Properties Are Your Friend

SQL Server Best Practices
SQL Server Best Practices
It’s nice to have friends, why aren’t you a friend of extended properties? They can make your like so much easier by helping to document your databases and objects.

Take a basic table create statement

CREATE TABLE [dbo].[EatSomeBoogers](
    [ID] [int] NOT NULL,
    [BgType] [varchar](20) NOT NULL,
    [Size] [smallint] NULL,
    [Viscosity] [tinyint] NULL
) ON [PRIMARY]


Pretty simple and we can infer a lot of the information about the table, but we mention size, are we measuring in inches, feet, yards?

We can add extended properties that will help to provide that sort of guidance for someone to reference later on.

EXEC sys.sp_addextendedproperty @name = N'Details',
    @value = N'Size is measured in mm', @level0type = N'SCHEMA',
    @level0name = N'dbo', @level1type = N'TABLE',
    @level1name = N'EatSomeBoogers', @level2type = N'COLUMN',
    @level2name = N'Size'
GO

We can also add properties at the table level:

EXEC sys.sp_addextendedproperty @name = N'Purpose',
    @value = N'Holds information about all the gold digging’,
    @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
    @level1name = N'EatSomeBoogers'
GO

And at the database level:

EXEC [MyDB].sys.sp_addextendedproperty @name = N'Usage',
    @value = N'Will handle all information as relates to digging' 
GO

You can even add extended properties to other objects, like stored procedures:

Continue reading on SirSQL.net.

54321
(0 votes. Average 0 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *