4

I'm pretty sure you can't do this like you can with foreign keys but I'd like to document at the database level (even if it's just as a bit of free text somewhere) these relationships.

Is there some way of adding comments to tables or something like that? I've noticed comments in the sql used to create things are often preserved but not sure how you edit these without recreating the whole object or how you view them without dumping ddl.

I'm interested in doing this in sqlserver and oracle. I'm primarily doing edits from the command line/scripts but often look at the databases using SSMS and Sql developer.

The reason I'm interested in this is to let other developers know how to use the database - both future devs + the ones I'm currently working with. We've got 3 applications using the same db and unfortunately its missing primary key and foreign key constraints in a lot of areas, whilst putting those in will go a long way to making it easier to understand, this would also help a lot aswell.

One hacky way I can think of doing it is adding an extra column to each table with the sequence name but I dont really like that - think its a bit misleading.

Improve this question
8
  • The first answers to this question have only addressed how to add comments to database objects; If I understand your rambling question correctly, you want to know what is a good way to document which sequences are used for which columns. Like I mentioned in my answer, you can solve this directly in SQL Server and the newest version of Oracle. Someone has suggested an edit to this question to remove the bit about "link tables and sequences" in the title, but that's the crux of the issue here: how to document this relationship. Comments are of course one way to do this.
    – Colin 't Hart
    Commented May 30, 2014 at 10:37
  • Is table:sequence 1:1 or many:1?
    – Aaron Bertrand
    Commented May 30, 2014 at 12:03
  • @AaronBertrand I couldn't say for sure but I expect 1:1
    – JonnyRaa
    Commented May 30, 2014 at 13:41
  • 1
    If it's 1:1 - say, you have a Sequence dedicated to Customers, why not just call it dbo.CustomerSequence? I don't think it gets any more self-documenting than that.
    – Aaron Bertrand
    Commented May 30, 2014 at 13:45
  • @AaronBertrand yeah that certainly seems like the simplest way for new stuff
    – JonnyRaa
    Commented May 30, 2014 at 13:59

3 Answers 3

Reset to default
4

Easy in Oracle:

comment on table mytable is 'This is a table comment';

comment on column mytable.mycolumn is 'This is a column comment';

You can then view the comments using the USER_TAB_COMMENTS and USER_COL_COMMENTS data dictionary views.

This answer covers it for SQL Server.

Improve this answer
1
  • hmm looks more hassle than its worth in sql server + in both it seems the comments aren't very discoverable. thanks for the answer though
    – JonnyRaa
    Commented May 30, 2014 at 9:52
4

For SQL Server, use:

  1. The IDENTITY column property (all versions); or
  2. CREATE SEQUENCE and NEXT VALUE FOR in a column default constraint (2012 onward).

For Oracle, upgrade to 12c and specify <sequence>.NEXTVAL as the default value for the column.

Problem solved!

Improve this answer
3
  • And to amen the other answerers, comment your database objects. Comments are a form of documentation, and documentation works best when it's kept close to what it documents, not as some rarely updated document kept somewhere else.
    – Colin 't Hart
    Commented May 30, 2014 at 10:24
  • nice - didn't know you can use sequence nextval as default. That's useful.
    – JonnyRaa
    Commented May 30, 2014 at 10:47
  • You can do a similar thing in SQL Server.
    – Aaron Bertrand
    Commented May 30, 2014 at 12:04
1

For MS SQL Server, it appears to be well described here. Personally, as a DBA, I would _NEVER_ let a programmer give me a script without comments on all tables and columns (written in clear English!).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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