Displaying extended properties of all tables in a schema using fn_listextendedproperty ()

fn_listextendedproperty() returns extended properties of database objects. This helps you to display extended properties on a database, all columns in a table and all tables in a schema.

Extended Property facilitates a very good documentation on each and every objects. It is one of the best practice to have extended property on an object...

-- Get extended properties on adatabase

USE DatabaseName
GO
SELECT objtype, objname, name, value
FROM fn_listextendedproperty(default, default, default, default, default, default, default );
GO

--Get extended properties on all columns in a table

SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'SchemaName ', 'table', 'TableName ', 'column', default);
GO

-- Get extended properties for all tables in one schema

SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'SalesSchema ', N'table', default, NULL, NULL);