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]
GO
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:
EXEC sys.sp_addextendedproperty @name = N'ProcUsage',
@value = N'Gets booger sizes and types', @level0type = N'SCHEMA',
@level0name = N'dbo', @level1type = N'PROCEDURE',
@level1name = N'GrabBoogers'
GO
What’s great is that you can then quickly and easily query the extended properties for your objects:
SELECT
OBJECT_NAME(ep.major_id) AS ObjectName ,
CASE
WHEN c.name IS NOT NULL then 'COLUMN'
else o.type_desc
END AS ExtendedPropertyType,
c.name AS 'ColumnName' ,
ep.name AS ExtendedPropertyName ,
ep.value AS ExtendedPropertyValue
FROM sys.extended_properties ep
LEFT JOIN sys.columns c ON ep.major_id = c.object_id
AND ep.minor_id = c.column_id
LEFT JOIN sys.objects o ON ep.major_id = o.object_id
ORDER BY ObjectName, ColumnName
This give you a really quick and easy way to document your objects. I highly recommend that during your next development project that you make life easy for yourself and add extended properties to your objects (and trust me, your DBA will thank you).
SirSQL.net 