December 15, 2010 at 3:30 pm
Other then running the query for each database one at a time, is there a way to list all table and or all column level Extended Propertys for all tables in every database on an instance of SQL Server?
Thanks
Kindest Regards,
Just say No to Facebook!December 18, 2010 at 12:15 pm
You can query sys.extended_properties in combination with sp_msforeachdb stored procedure.
EXEC dbo.sp_msforeachdb 'SELECT ''?'' db_name, OBJECT_NAME(ex.major_id) object_name, ex.name ext_prop_name,
ex.value ext_prop_value
FROM (
SELECT c.object_id, c.column_id
FROM [?].information_schema.columns isc
INNER JOIN [?].sys.columns c ON isc.column_name = c.name
) col
JOIN [?].sys.extended_properties ex
ON ex.major_id = col.object_id
AND ex.minor_id = col.column_id'
January 10, 2011 at 11:22 am
Thanks for the tip. I do wish Microsoft would provide more T-SQL like options for this kind of thing though. I know the 'sp_msForEachDB' and 'sp_msForEachTable' are popular but I for one prefer methods that don't require the use of these. I would have hoped they'd have a manageent view for this kind of thing by 2008R2 if not by version 2008.
Thanks again
Kindest Regards,
Just say No to Facebook!Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply