Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Table documentation advice Expand / Collapse
Author
Message
Posted Wednesday, December 26, 2012 7:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:05 AM
Points: 102, Visits: 581
Hi,

First of all I hope everyone has had a good Christmas!

Just wanted to know if anyone documents there table using MS_Description? I'm struggling to think how much detail I should put on there so the end user understands what this table entails. I would also like to say which ssis package populates this table or if this gets populated by other source of etl process.

Just need an example I can look at and I can then use that best example from now on.

Thanks
Post #1400233
Posted Wednesday, December 26, 2012 7:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 13,061, Visits: 11,889
SQL_Kills (12/26/2012)
Hi,

First of all I hope everyone has had a good Christmas!

Just wanted to know if anyone documents there table using MS_Description? I'm struggling to think how much detail I should put on there so the end user understands what this table entails. I would also like to say which ssis package populates this table or if this gets populated by other source of etl process.

Just need an example I can look at and I can then use that best example from now on.

Thanks


End users should not be seeing your table definitions. This type of documentation is for technical people (dbas, sql devs, etc). People that can view the table definitions. That being said, I don't find it to be very useful because you have to dig around to even realize the information is there.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1400249
Posted Wednesday, December 26, 2012 9:10 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 1,948, Visits: 2,882
Sean Lange (12/26/2012)
SQL_Kills (12/26/2012)
Hi,

First of all I hope everyone has had a good Christmas!

Just wanted to know if anyone documents there table using MS_Description? I'm struggling to think how much detail I should put on there so the end user understands what this table entails. I would also like to say which ssis package populates this table or if this gets populated by other source of etl process.

Just need an example I can look at and I can then use that best example from now on.

Thanks


End users should not be seeing your table definitions. This type of documentation is for technical people (dbas, sql devs, etc). People that can view the table definitions. That being said, I don't find it to be very useful because you have to dig around to even realize the information is there.



I somewhat disagree. I quite agree that most end-users should not be able to get table documentation, per se, since they should not be aware of tables. Typically, however, a few selected "power users" can use this info.

I generate documentation into the extended attributes, although I store the original documentation in a database. I can then generate to other formats also (html, xml, etc.).


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1400272
Posted Wednesday, December 26, 2012 9:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 13,061, Visits: 11,889
ScottPletcher (12/26/2012)
Sean Lange (12/26/2012)
SQL_Kills (12/26/2012)
Hi,

First of all I hope everyone has had a good Christmas!

Just wanted to know if anyone documents there table using MS_Description? I'm struggling to think how much detail I should put on there so the end user understands what this table entails. I would also like to say which ssis package populates this table or if this gets populated by other source of etl process.

Just need an example I can look at and I can then use that best example from now on.

Thanks


End users should not be seeing your table definitions. This type of documentation is for technical people (dbas, sql devs, etc). People that can view the table definitions. That being said, I don't find it to be very useful because you have to dig around to even realize the information is there.



I somewhat disagree. I quite agree that most end-users should not be able to get table documentation, per se, since they should not be aware of tables. Typically, however, a few selected "power users" can use this info.

I generate documentation into the extended attributes, although I store the original documentation in a database. I can then generate to other formats also (html, xml, etc.).


Sounds to me like we didn't disagree at all. We both said it is for a few "power users".

I would be curious how you use the extended properties to help generate documentation. I have never really found the extended properties very useful. Do you use them to generate help files / technical documentation?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1400284
Posted Wednesday, December 26, 2012 10:18 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 1,948, Visits: 2,882
Sean Lange (12/26/2012)
ScottPletcher (12/26/2012)
Sean Lange (12/26/2012)
SQL_Kills (12/26/2012)
Hi,

First of all I hope everyone has had a good Christmas!

Just wanted to know if anyone documents there table using MS_Description? I'm struggling to think how much detail I should put on there so the end user understands what this table entails. I would also like to say which ssis package populates this table or if this gets populated by other source of etl process.

Just need an example I can look at and I can then use that best example from now on.

Thanks


End users should not be seeing your table definitions. This type of documentation is for technical people (dbas, sql devs, etc). People that can view the table definitions. That being said, I don't find it to be very useful because you have to dig around to even realize the information is there.



I somewhat disagree. I quite agree that most end-users should not be able to get table documentation, per se, since they should not be aware of tables. Typically, however, a few selected "power users" can use this info.

I generate documentation into the extended attributes, although I store the original documentation in a database. I can then generate to other formats also (html, xml, etc.).


Sounds to me like we didn't disagree at all. We both said it is for a few "power users".

I would be curious how you use the extended properties to help generate documentation. I have never really found the extended properties very useful. Do you use them to generate help files / technical documentation?



I do it the other way around: I store the documentation in a table, and use that table to generate the extended properties.

However, I also have code that allows me to take changes made to the extended properties and put them back into the documentation table. Sometimes it's easier to make quick changes there, or I notice something while I'm using the documentation and so correct it right there.

I also encourage end users to do their own documentation for various apps, "screens", etc.. Often they can phrase things so that they, and others in that specific job/function, understand them better. I leverage the existing documentation system, right now thru the SSMS extended attributes editor (not great, but at least they have some type of GUI ). Later hopefully through Word and other more powerful means.

I, too, never found extended properties that useful either before, particularly since I had to code them by hand .

But now I have procs that cover all that, I have to admit I rather like being able to use extended properties to get a quick description of a table column immediately from SSMS; when, for example, I can't remember what a code of "S" means for a particular column (even when I created that column originally ).

The documentation table has flags that indicate who, by role, should see which comments: DBA-only, developer admin, developer, power user, any user, etc..

I'm sure it's not optimal, but it gives me something in the way of documentation; in particular, it allows me to document as the table is created, when I know the most about it.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1400296
Posted Wednesday, December 26, 2012 10:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 13,061, Visits: 11,889
ScottPletcher (12/26/2012)
Sean Lange (12/26/2012)
ScottPletcher (12/26/2012)
Sean Lange (12/26/2012)
SQL_Kills (12/26/2012)
Hi,

First of all I hope everyone has had a good Christmas!

Just wanted to know if anyone documents there table using MS_Description? I'm struggling to think how much detail I should put on there so the end user understands what this table entails. I would also like to say which ssis package populates this table or if this gets populated by other source of etl process.

Just need an example I can look at and I can then use that best example from now on.

Thanks


End users should not be seeing your table definitions. This type of documentation is for technical people (dbas, sql devs, etc). People that can view the table definitions. That being said, I don't find it to be very useful because you have to dig around to even realize the information is there.



I somewhat disagree. I quite agree that most end-users should not be able to get table documentation, per se, since they should not be aware of tables. Typically, however, a few selected "power users" can use this info.

I generate documentation into the extended attributes, although I store the original documentation in a database. I can then generate to other formats also (html, xml, etc.).


Sounds to me like we didn't disagree at all. We both said it is for a few "power users".

I would be curious how you use the extended properties to help generate documentation. I have never really found the extended properties very useful. Do you use them to generate help files / technical documentation?



I do it the other way around: I store the documentation in a table, and use that table to generate the extended properties.

However, I also have code that allows me to take changes made to the extended properties and put them back into the documentation table. Sometimes it's easier to make quick changes there, or I notice something while I'm using the documentation and so correct it right there.

I also encourage end users to do their own documentation for various apps, "screens", etc.. Often they can phrase things so that they, and others in that specific job/function, understand them better. I leverage the existing documentation system, right now thru the SSMS extended attributes editor (not great, but at least they have some type of GUI ). Later hopefully through Word and other more powerful means.

I, too, never found extended properties that useful either before, particularly since I had to code them by hand .

But now I have procs that cover all that, I have to admit I rather like being able to use extended properties to get a quick description of a table column immediately from SSMS; when, for example, I can't remember what a code of "S" means for a particular column (even when I created that column originally ).

The documentation table has flags that indicate who, by role, should see which comments: DBA-only, developer admin, developer, power user, any user, etc..

I'm sure it's not optimal, but it gives me something in the way of documentation; in particular, it allows me to document as the table is created, when I know the most about it.


I know what you mean about something like an "S".

If you have any details about you use the extended properties (code, etc) I would be interested in reading it. It sounds like a pretty interesting way to go about it. I am always open to new ideas. It may be too that what you have is difficult to share because it sounds like a number of things that make up the whole picture. Hmmm...sounds like an article in the making.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1400300
Posted Wednesday, December 26, 2012 11:17 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 1,948, Visits: 2,882
Sean Lange (12/26/2012)
ScottPletcher (12/26/2012)
Sean Lange (12/26/2012)
ScottPletcher (12/26/2012)
Sean Lange (12/26/2012)
SQL_Kills (12/26/2012)
Hi,

First of all I hope everyone has had a good Christmas!

Just wanted to know if anyone documents there table using MS_Description? I'm struggling to think how much detail I should put on there so the end user understands what this table entails. I would also like to say which ssis package populates this table or if this gets populated by other source of etl process.

Just need an example I can look at and I can then use that best example from now on.

Thanks


End users should not be seeing your table definitions. This type of documentation is for technical people (dbas, sql devs, etc). People that can view the table definitions. That being said, I don't find it to be very useful because you have to dig around to even realize the information is there.



I somewhat disagree. I quite agree that most end-users should not be able to get table documentation, per se, since they should not be aware of tables. Typically, however, a few selected "power users" can use this info.

I generate documentation into the extended attributes, although I store the original documentation in a database. I can then generate to other formats also (html, xml, etc.).


Sounds to me like we didn't disagree at all. We both said it is for a few "power users".

I would be curious how you use the extended properties to help generate documentation. I have never really found the extended properties very useful. Do you use them to generate help files / technical documentation?



I do it the other way around: I store the documentation in a table, and use that table to generate the extended properties.

However, I also have code that allows me to take changes made to the extended properties and put them back into the documentation table. Sometimes it's easier to make quick changes there, or I notice something while I'm using the documentation and so correct it right there.

I also encourage end users to do their own documentation for various apps, "screens", etc.. Often they can phrase things so that they, and others in that specific job/function, understand them better. I leverage the existing documentation system, right now thru the SSMS extended attributes editor (not great, but at least they have some type of GUI ). Later hopefully through Word and other more powerful means.

I, too, never found extended properties that useful either before, particularly since I had to code them by hand .

But now I have procs that cover all that, I have to admit I rather like being able to use extended properties to get a quick description of a table column immediately from SSMS; when, for example, I can't remember what a code of "S" means for a particular column (even when I created that column originally ).

The documentation table has flags that indicate who, by role, should see which comments: DBA-only, developer admin, developer, power user, any user, etc..

I'm sure it's not optimal, but it gives me something in the way of documentation; in particular, it allows me to document as the table is created, when I know the most about it.


I know what you mean about something like an "S".

If you have any details about you use the extended properties (code, etc) I would be interested in reading it. It sounds like a pretty interesting way to go about it. I am always open to new ideas. It may be too that what you have is difficult to share because it sounds like a number of things that make up the whole picture. Hmmm...sounds like an article in the making.



Yes, unfortunately it is somewhat involved. Moreover, I normalized the documentation_* tables, so there are a number of them.

But you have great T-SQL skills, so I know you could do the same as far as taking text in a varchar(max) column and coverting it to code to add extended properties, or to html pages, etc., once you got the documentary text into that column.

For example, here's a small subset of very basic html I generated from the table, to help people working on some new tables we created. Naturally I removed all the actual Logins, but it willl give you the general idea. This is the rough-and-ready version for developers who just need quick descriptions to work with new tables I've created, not the more "cutesy" end-user versions.

Now I just have to figure out how to attach the html ...


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1400311
Posted Wednesday, December 26, 2012 11:30 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 1,948, Visits: 2,882
Used .rtf instead.

The hyperlinks don't work, but it still gives you the flavor.

The underlying html is generated from a table that contains the text documentation.

I'm still working to fully extended it to support BLOBs such as ER diagrams, etc..

But I can quickly gen any subset of SQL entity documentation to match what a DBA / developer / user is working on at that moment.

I also store DBA-type documentation, such as web info on "undocumented" XPs, DBCC commands, how to resolve specific issues, etc. in the documentation_* tables, then gen those out periodically into my own quick-lookup html file.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker


  Post Attachments 
html_test.rtf (37 views, 235.50 KB)
Post #1400312
Posted Wednesday, December 26, 2012 12:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 13,061, Visits: 11,889
Thanks for sharing Scott. That gives me an idea of what you are doing here. Never really thought of using the extended properties like that. Gives me some cool ideas for extending a utility I have built already.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1400321
Posted Wednesday, December 26, 2012 3:18 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 1,948, Visits: 2,882
Btw, the column data types are not stored in my documentation tables -- I retrieve them at gen time from system views. This adds a little overhead, but insures that the data types are current and don't have to be continually updated.

SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1400395
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse