In SQL Server, what do you guys use extended properties for? Some articles and blogs are suggesting they be used for self-documentation of the database and its objects but I can't see this being used properly... is this largely ignored by most?
Largely ignored, as you're saying.
Extended properties are a bit awkward to read and write both from T-SQL and GUI. IMHO documentation should be maintained elsewhere (database projects, project documentation etc.).
Here's a good article on extended properties, which also addresses some of your doubts.
I'm working with a system where we have numerous databases. Self-documenting is useful because each database can have a different structure.
We manage a database-structure version process and store that information in the extended properties. We also capture descriptions of tables, columns and other database objects.
Working with extended properties would be unmanageable without help from automation though. We developed tools that help us capture and store the information in the extended properties. And, we also have tools to view and report the information.
Developing the tools has been useful in this environment, but I don't see the benefit if your working in a shop where you have very few databases.
I have not yet seen a single project that used extended properties. IMO the reason is this: even if we want to store documentation in the database, which is usually not the case, there are alternatives. Usually extended properties do not do exactly what we want. On the other hand, rolling out our own solution that does exactly what we need is so easy, so why bother?
In our projects we use them for keeping versioning information in extended properties set per database.
Eg: We use Team Foundation projects and we track in a db extended property the last post deployment script number that was executed from the post deployment folder (eg - we have scripts 1 to 23, the ext. property is set to 15, so the post deployment script will execute only scripts from 16 and further). Same for pre-deployment scripts.
I agree that we could keep that info in a table, but it's a way to use extended properties.
Here's an example data dictionary script that makes heavy use of table and column descriptions that shows how useful they can be for self documenting, http://www.csvreader.com/posts/data_dictionary.php .