I’m playing around with using extended properties in my SQL Build Scripts so i can track what version my servers are up to. I thought it would be handy (for me at least) to create a by example on using the functions, proces and tables that are used to deal with them.

 

Viewing and Adding Extended Properties with SSMS

Simply right click on any object -> Properties and select Extended Properties
extendedProperties01

extendedProperties02

 

/*

Extended Properties 

READ : sys.fn_listextendedproperty
	http://technet.microsoft.com/en-us/library/aa258890(v=sql.80).aspx
READ : sys.extended_properties
	http://msdn.microsoft.com/en-us/library/ms177541.aspx
ADD : sp_addextendedpropert
	http://technet.microsoft.com/en-us/library/ms180047(v=sql.105).aspx
UPDATE : sp_updateextendedproperty 
	http://technet.microsoft.com/en-us/library/ms186885(v=sql.105).aspx
DROP : sp_dropextendedproperty 
	http://technet.microsoft.com/en-us/library/ms178595(v=sql.105).aspx

List of all objects that can have extended properties
http://technet.microsoft.com/en-us/library/ms190243(v=sql.105).aspx

*/

/*
---------------------------------------------------------------------------------------------------
READ : sys.fn_listextendedproperty, sys.extended_properties
---------------------------------------------------------------------------------------------------
*/
-- Read all extended properties for an object
SELECT  *
FROM    sys.extended_properties
WHERE   major_id = OBJECT_ID('MyTable')

-- Read extended property for all objects in database via extended_properties TABLE (join all_objects for Name
SELECT  type_desc AS 'ObjectType' ,
        ao.name AS 'ObjectName' ,
        ep.name AS 'ExtendedPropertyName' ,
        value AS 'ExtendedPropertyValue'
FROM    sys.extended_properties AS ep
        INNER JOIN sys.all_objects ao ON ep.major_id = ao.object_id 



/*
---------------------------------------------------------------------------------------------------
ADD : sp_addextendedpropert
---------------------------------------------------------------------------------------------------
*/
-- Add extended property to database
EXEC sys.sp_addextendedproperty @name = N'DatabaseDetails',
    @value = 'AdventureWorks2012 is a Codeplex OLTP Database';

-- Add extended property to filegroup
EXEC sys.sp_addextendedproperty @name = 'Information',
    @value = 'Secondary filegroup for indices', @level0type = 'FILEGROUP',
    @level0name = 'Ind';

-- Add extended property to schema
EXEC sys.sp_addextendedproperty @name = 'schemaDescription',
    @value = 'Read only schema', @level0type = 'SCHEMA', @level0name = 'RO';

-- Add extended property to table 
EXEC sys.sp_addextendedproperty @name = N'versionNo', @value = N'1',
    @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
    @level1name = N'myTable';

-- Add extended property to index
EXEC sys.sp_addextendedproperty @name = N'Description',
    @value = N'Nonclustered index covering..', @level0type = N'SCHEMA',
    @level0name = 'dbo', @level1type = N'TABLE', @level1name = 'myTable',
    @level2type = N'INDEX', @level2name = 'myIndex';

-- Add extended property to stored procedure
EXEC sys.sp_addextendedproperty @name = N'Version', @value = N'50.1',
    @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE',
    @level1name = 'usp_super';

-- Add extended property to function
EXEC sys.sp_addextendedproperty @name = 'Description',
    @value = 'details of my function', @level0type = 'SCHEMA',
    @level0name = 'dbo', @level1type = 'FUNCTION', @level1name = 'myFunction';

-- Add extended property to trigger
EXEC sys.sp_addextendedproperty @name = 'Description',
    @value = 'details of my function', @level0type = 'SCHEMA',
    @level0name = 'dbo', @level1type = 'TRIGGER', @level1name = 'myTrigger';

-- Add extended property to column
EXEC sys.sp_addextendedproperty @name = N'Description',
    @value = 'FK linking to department table', @level0type = N'Schema',
    @level0name = 'dbo', @level1type = N'Table', @level1name = 'Employee',
    @level2type = N'Column', @level2name = 'departmentId';

-- Add extended property to role
EXEC sys.sp_addextendedproperty @name = 'Description',
    @value = 'powerUsers permissions give x and y', @level0type = N'USER',
    @level0name = 'PowerUsers';

-- Add extended property to login
EXEC sys.sp_addextendedproperty @name = N'Description',
    @value = N'Read only user', @level0type = N'USER',
    @level0name = N'ReadOnly_MyAPP';

-- Add extended property to type
EXEC sys.sp_addextendedproperty @name = 'Description',
    @value = 'My added type', @level0type = 'SCHEMA', @level0name = 'dbo',
    @level1type = 'TYPE', @level1name = 'myType';



/*
---------------------------------------------------------------------------------------------------
UPDATE : sp_updateextendedproperty 
---------------------------------------------------------------------------------------------------
*/

-- Update extended property to database
EXEC sys.sp_updateextendedproperty @name = N'DBVersion', @value = '12.2';

-- Update extended property to table
EXEC sys.sp_updateextendedproperty @name = 'Version', @value = '50.2',
    @level0type = N'Schema', @level0name = 'dbo', @level1type = N'Table',
    @level1name = 'myTable';



/*
---------------------------------------------------------------------------------------------------
DROP : sp_dropextendedproperty 
---------------------------------------------------------------------------------------------------
*/

-- Drop extended property on database
EXEC sys.sp_updateextendedproperty @name = 'Description';

-- Drop extended properly on table
EXEC sys.sp_updateextendedproperty @name = 'caption', @level0type = 'SCHEMA',
    @level0name = 'dbo', @level1type = 'TABLE', @level1name = 'myTable';





 

Advertisements