Query for extended properties

Question

Post reply

Query for extended properties

maxlezious

SSCommitted

Points: 1648
More actions
March 7, 2013 at 2:25 am

#274962

Hi,
Can anyone help me with a query which will select all the stored procedures with their Extended Properties.
Thanks a lot.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Here is an excellent source for information on Extended Properties by Glenn Berry

http://sqlserverperformance.wordpress.com/2011/01/10/fun-with-extended-properties-in-sql-server-2008/

which I believe will provide you with the information you requested.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

maxlezious SSCommitted Points: 1648 More actions · Answer 2

maxlezious

SSCommitted

Points: 1648

March 7, 2013 at 7:20 am

#1594442

Thank you Ron, appreciate your help.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

For my own learning experience, following Glenn Berry's blog posting here is a query I fashioned

DECLARE @LookF VARCHAR(9)

SET @LookF = 'procedure'

SELECT

CAST(Value AS nvarchar(500)) AS [MS_Description]

FROM sys.extended_properties

WHERE CAST(Value AS nvarchar(500)) LIKE ('%'+@lookf+'%')

and using AdventureWorks2012 it worked ..

Please post back the T-SQL that you used in your situation, so that others with a similar problem can benefit.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

maxlezious SSCommitted Points: 1648 More actions · Answer 4

Hi Ron,

Following is the TSql I used:

SELECT OBJECT_NAME(EXP.major_id) AS TableName,

C.name AS ColumnName,

EXP.name AS PropertyName,

EXP.value AS PropertyValue

FROM sys.extended_properties AS EXP

LEFT OUTER JOIN sys.columns AS C

ON C.object_id = EXP.major_id

AND C.column_id = EXP.minor_id

WHERE EXP.class_desc = 'OBJECT_OR_COLUMN'

Kind Regards

Lowell SSC Guru Points: 323504 More actions · Answer 5

this is a view that I create to show me all the extended properties:

CREATE VIEW VW_CURRENT_EXTENDED_PROPERTIES

AS

SELECT

'' AS [Object_Schema],

DB_NAME() AS [Object_Name],

'DATABASE' AS [ObjectType],

'' AS [ColumnName],

propz.[class],

propz.[class_desc],

propz.[major_id],

propz.[minor_id],

propz.[name],

propz.[value]

FROM sys.extended_properties propz

WHERE propz.[class] = 0

UNION ALL

SELECT

SCHEMA_NAME(objz.schema_id) AS [Object_Schema],

objz.name AS [Object_Name],

objz.type_desc AS [ObjectType],

'' AS [ColumnName],

propz.[class],

propz.[class_desc],

propz.[major_id],

propz.[minor_id],

propz.[name],

propz.[value]

FROM sys.objects objz

LEFT OUTER JOIN sys.extended_properties propz

on objz.object_id = propz.major_id

WHERE propz.[class] = 1

AND propz.[minor_id] = 0

UNION ALL

SELECT

SCHEMA_NAME(objz.schema_id) AS [Object_Schema],

objz.name AS [Object_Name],

objz.type_desc AS [ObjectType],

colz.name AS [ColumnName],

propz.[class],

propz.[class_desc],

propz.[major_id],

propz.[minor_id],

propz.[name],

propz.[value]

FROM sys.objects objz

LEFT OUTER JOIN sys.columns colz

ON colz.OBJECT_ID=objz.OBJECT_ID

LEFT OUTER JOIN sys.extended_properties propz

ON colz.OBJECT_ID = propz.major_id

AND colz.column_id = propz.minor_id

WHERE propz.[class] = 1

AND propz.[minor_id] <> 0

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

maxlezious SSCommitted Points: 1648 More actions · Answer 6

Thank you Lowell for creating a view for me. Its been a great help.

Kind Regards