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

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 565
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);

Comments or Responses

Login to post response