The more SQL fields I add to my tables and the more primary/foreign keys I add the more I lose the overview for specific fields for certain scenarios like Get/Add/Delete/Update data.

I use SQL Management Studio (SQL Server) to design my database in the database diagram designer.

Maybe there is a better tool or good approach how to keep track of the meaning of all those fields?

5 Answers 5


How about a logical semantically correct naming system that avoids duplication, tautology and abbreviations?

That and a Glossary and a logical / physical ER diagram of the database, preferably hosted on a Wiki of some sort, is about all you can do.

Plus what Jason Holland says in the comment below! :-)

  • 4
    semantically correct naming system that avoids duplication, tautology and abbrevations THIS! Plus with SQL Server you can put descriptions of each field, which are tied to the table itself.
    – programmer
    Jul 11, 2012 at 13:50
  • The Description field in management studio is more of a hack... Not usable in any productive way.
    – Pascal
    Jul 11, 2012 at 19:25
  • 1
    The description field is an extended property which is stored within the database. This can be used in a productive way, as an example: A previous environment I worked in used extended properties in conjunction with a CodeSmith generator product that would generate all of our stored procedures for read, inserts and deletes. We also utilized extended properties to build our data dictionaries.
    – user60909
    Aug 5, 2012 at 4:22

SQL Management Studio has the ability to add a Description for columns, but I've never found it useful, ever. I've also tried to get work to use docs, wikis and what-have-you's to document DB structure. After enough time, nobody bothers though. Release dates won't wait for anything.

A descriptive, consistent naming scheme won't fail you. Don't be afraid to use longer and descriptive column names if you feel the need, it's better to ensure future-you can make sense of the structure.

Currently I'm working a a 1600+ column DB that is a nightmare of inconsistent naming; so stick to your style!

A suggestion:

  • name the primary key column to include it's purpose, so not "ID", but "ClientID". Makes reading queries easier.
  • decide and stick to table names being singular or plural (i.e. tblClient vs tblClients)
    • My logical self prefers the singular
  • prefer to group similar tables together, i.e. prefix lookup/static tables with tblLUT / lookup_
  • the same goes for grouping Clients, Jobs, Assets, Cheezeburgers and similar
  • use stored procedures and custom views, a lot of them, to retrieve your data
    • this normalizes your data access, providing a higher layer of data access

On a semi-related note, I recently found an alternative SQL server tool, with this nifty feature to generate code classes based on your table structures, taking a data reader in the constructor, makes for real handy work. It has made me realize that naming tables and fields without tbl and f prefixes is kind of nice for tools like these. (SQL Anywhere by Atlantis Interactive)

  • 4
    And using tbl and f prefixes for tables/fields strikes me as rather absurd - what use can that possibly be? Or do you name all your classes starting with C and and all your variables with v?
    – sleske
    Jul 11, 2012 at 9:45
  • 2
    For a discussion of possible problems with stored procedures, see e.g. programmers.stackexchange.com/questions/65742/… and programmers.stackexchange.com/questions/139319/… I'm not saying they are always bad, but there are problems involved.
    – sleske
    Jul 11, 2012 at 10:16
  • 1
    Hungarian notation for database tables and columns?? No thanks. That would make a very funny code when you try to use an ORM tool, unless you want to manually rename ALL the table-class and field-property mappings.
    – Konamiman
    Jul 11, 2012 at 13:58
  • 1
    Stored procedures are not controversial. It all depends on your goals. It comes down to the question of if you plan on sticking with the same database vendor or not. If one plans on sticking with SQL Server then stored procedures are definitely not controversial.
    – programmer
    Jul 11, 2012 at 13:59
  • 1
    At work we have to track the timestamp emails were sent. I have a few columns like so: MonthlySummaryEmailLastSent, WelcomeEmailLastSent etc. Make them as long as you need to get your point across. Aug 5, 2012 at 5:17

I would suggest to group your tables in logical domains and visualize them on a poster. You may created and prepare printable file them with the help of ER studio/SSMS.

Most importantly, naming of columns need to be closely related to domain to avoid naming further ambiguity in long run.


Since you're using MSSQL, if you're able to deploy a website then put together a little site that queries the system catalogs for the information you want to document. Connect to your database and go to Views -> System Views to see them.

I'd ignore the views in INFORMATION_SCHEMA because while they provide a more user-friendly format if you just want to look at them directly, they're lacking some useful information. The views in the "sys" schema contain everything.

The ones you'll most likely want to start looking at are:


If you have any problems figuring out the meanings of columns in those views, they're all documented in Books Online / MSDN. Most of those should be self explanatory, except maybe sys.extended_properties. That's where Management Studio saves descriptions. If you add a description to a table and save it, then right-click the table in Object Explorer, and select Properties then Extended Properties you'll see the description. You can add an arbitrary amount of extended properties to things, and this can come in real handy for documentation depending on what you want to document and how detailed you want to get.

If you want to allow descriptions to be edited in the site itself, you can use the sys.sp_addextendedproperty, sys.sp_updateextendedproperty, and sys.sp_dropextendedproperty stored procedures.

Finally, if you want to document where a table / view / etc. is referenced throughout the database, you can use the sys.sp_depends stored procedure to get that information.

The nice thing about this approach is that your documentation will be exactly as up to date as the database itself. Looking at a table definition and add a new column to the table? Refresh the page and it's there. Although, if you want to have documentation that's just a snapshot, you can do that too. And you get something you can navigate through and in a format that avoids the clunkiness of trying to read description fields in SSMS.


The best naming in the world can't help you to know the details of why a field exists or what to look out for in the data. Check out Database Note Taker which is a free lighter weight tool enabling you to add an explanation to your database objects. Could be a short note, could be a longer description with example queries and using markdown syntax for formatting. It keeps your comments in a separate project file which is source control friendly.

  • 1
    Usually it is best to keep your source (database schema) and documentation as close together as practical, so you will be triggered to update your documentation when the source changes. Jun 20, 2014 at 6:59
  • If all developers have their development databases connected to SQL Source control or similar then yes but that would require a significant good budget for Red Gate's tool. In our case we have several developers each with multiple versions of the same database and we find it really simple to just open DNT, sync the database and then it shows where comments are missing and we add them, and at an affordable cost to our company. I guess it depends on how you are managing your database development but keeping comments in development databases does not work for us.
    – Action Dan
    Jun 20, 2014 at 8:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.