Menu
I’ve been tasked with creating a query that will return following data:
[Schema Name], [Object Name], [Extended Property Name], [Extended Property Value]
Any ideas how to accomplish this? I’ve found fn_listextendedproperty function but that doesn’t help much.
329
We can add extended properties to almost all SQL Server objects by right clicking on the object-> select properties-> select “Extended Properties” and add new properties to the object. So, after adding the new properties we might need to query those properties in the future. All done!
Extended properties are a useful feature in SQL Server which can be used for documentation and content purposes. The properties can be updated for tables, views, triggers and so on. Developers can use this feature for extensive database objects which can be used for reference for many SQL Server objects.
When the object type is specified and the value of the corresponding object name is NULL or default, fn_listextendedproperty returns all extended properties for all objects of the type specified. The objects are distinguished according to levels, with level 0 as the highest and level 2 the lowest.
We can use system catalog view sys. objects to view all objects in a SQL database. It has a column type that contains the object category. For example, if we want to search only for the user-defined table, we use 'U' value for the type column.
The following example displays all extended properties set on the database object itself. USE AdventureWorks2012; GO SELECT objtype, objname, name, value FROM fn_listextendedproperty (default, default, default, default, default, default, default); GO Here is the result set.
Get Extended Property Using fn_listextendedproperty. We can also use the fn_listextendedproperty function to get a list of Extended Properties. The function returns objtype, objname, name with datatype sysname and value as sql_varient.
NULL can be used as a parameter for the object name to return multiple extended properties, but it is not as straight forward as you would think it should be, to use this function. The function can be found in the master database under Programmability > Functions > System Functions.
Scripting a selection of database objects by right-clicking on the database and selecting Tasks=>Generate Scripts will by default also generate the extended properties - but only if you choose to script DROP or CREATE scripts too.
Use this query which is column specific:
SELECT S.name as [Schema Name], O.name AS [Object Name], ep.name, ep.value AS [Extended property]
FROM sys.extended_properties EP
INNER JOIN sys.all_objects O ON ep.major_id = O.object_id
INNER JOIN sys.schemas S on O.schema_id = S.schema_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
Use this query for all extended properites:
SELECT S.name as [Schema Name], O.name AS [Object Name], ep.name, ep.value AS [Extended property]
FROM sys.extended_properties EP
LEFT JOIN sys.all_objects O ON ep.major_id = O.object_id
LEFT JOIN sys.schemas S on O.schema_id = S.schema_id
LEFT JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
