Tag Info

Hot answers tagged

39

I have been using extended properties. They are very flexible. Most standard documentation tools can be driven off MS_Description, and then you can use your own with custom-built tools. See this presentation: http://www.sqllunch.com/Lunches.aspx?Month=November%202010# And this code: http://code.google.com/p/caderoux/wiki/LeversAndTurtles


35

Microsoft's Visio Pro can reverse engineer a database as can CA's ERwin. Visio is the cheaper option, but ERwin is the more detailed, more complete option. Extended properties are nice, if people bother to look at them. You could also use something like Red Gate's SQL Doc to output documentation in HTML format. I find naming conventions and properly setup ...


15

Try SchemaSpy: http://schemaspy.sourceforge.net/


11

If it is ever written, the documentation consists of a word document. A couple of relationship diagrams will be included. Lists of the tables and a brief description of what each table holds and how it relates to other tables. One chapter of the documentation includes the security settings: what permissions does the "user" that the application need? ...


11

Take a look at SchemaCrawler - it is my free, command-line tool that I designed to do what you are looking for. SchemaCrawler produces a text file with all of database schema objects. This text output is designed to be both human-readable, as well as diff-able against similar output from another server. In practice, what I have found is that outputting a ...


11

For SQL Server I'm using extended properties. With the following PowerShell Script I can generate a Create Table scripts for single table or for all tables in the dbo schema. The script contains Create table, primary keys and indexes. Foreign keys are added as comments. The extended properties of tables and table columns are added as comments. Yes multi ...


8

In Oracle, you would use the COMMENT command to: [...] add to the data dictionary a comment about a table or table column, view, materialized view, operator, indextype, mining model, or edition. Most tools (PL/SQL Developer, Toad...) will display these comments in appropriate fields when you browse the database schema. The comments can be queried ...


8

Funny, I was wondering how other people are doing this as well.. While developing my first big database project, I found that Microsoft SQL Server Management Studio 10.0.1600.22 supports database diagrams which you can export to a word document or other documentation software where you can add as much documentation detail as you want. Just expand the ...


7

I use extended properties and Red Gates SQL Doc. Works very well!


7

I use data modeling tools because they allow me to document important information about the database other than what "fits" in a database. Meta data like privacy/security/sensitivity concerns, stewardship, governance, etc. That may go beyond what some need in documenting a database, but those things are important to the business and helping them manage ...


6

In Oracle there are table and column comments that can be used for documentation. These comments can easily be added by the following commands: COMMENT ON TABLE my_table IS 'Documentation of my table' / COMMENT ON COLUMN my_table.my_columns IS 'Documentation of my column' /


6

I set the MS_description extended property for all objects and then document the whole database using ApexSQL Doc. I used to create HTML documents earlier, but lately I prefer PDF


5

DB Dictionary Creator is an open source database documentation tool with decent GUI and export / import options. It uses Extended properties to store the documentation. It'll also generates automatic descriptions for primary key columns and foriegn key columns.


5

an user process is a piece of software that can connect to an oracle server. You (the user) can start a piece of this kind of software, then connect to oracle. Not quite. A User Process in Oracle is different than a client, which is what I think you are referring to. The user starts a client program (SQL*Plus, Pro*C, etc.), which runs on the ...


5

Microsoft reserves the right to change the binary representation of the build-in data types like DATETIME. So if you do an INSERT dbo.table(binaryColumn)VALUES(CAST(GETDATE() AS VARBINARY(8))); and then you upgrade SQL Server or even do an SP apply SELECT CAST(binaryColumn AS DATETIME) FROM dbo.table; might get you a different date then the one ...


5

To ensure you can see the objects you are looking for: Make sure you are in the right database / right instance - seems simple and silly but it happens all the time. This also includes not using things like AttachDbFileName in your connection string - this means that two different programs will actually have two separate copies of your database, so if one ...


4

The RedGate SQL Dependency Tracker tool might be helpful. It can show you graphically which database objects (SP's/views/tables) depend on each other. I've used it while working with some tables I was unfamiliar with to determine the order in which to disable constraints. I also ran it on the whole database just for fun and it was way TMI. If you are ...


4

The most important thing about documentation is that it makes sense to you. There's no really standard way of doing this. If you've got lots of stored procedures that all connect to each other starting with a Visio diagram with one object for each procedure, then links between them so you can track how things go from procedure to procedure is probably a ...


4

For Documenting sql server, I highly recommend just recently released : SQL Server & Windows Documentation Using Windows PowerShell written by Kendal Van Dyke Brief description from the link : SQL Power Doc is a collection of Windows PowerShell scripts and modules that discover, document, and diagnose SQL Server instances and their underlying Windows ...


3

Indeed, Extended Properties (MS_Description) is the way to go. Having these descriptions readily available as a part of the metadata could be utilized not only by docs generators but also (hopefully one day) by tools that provide "intellisense" for example the excellent Softtree's SQL Assistant http://www.softtreetech.com/isql.htm (last time I checked they ...


3

You could also take a look at wsSqlSrvDoc. It's a nice little tool that works with SQL Server extended properties and creates a MS Word document. The print-out of all column properties (with foreign key relations) works out of the box. For further descriptions on each field you have to set up extended properties of those columns in SQL Server Management ...


3

Paul White turned me onto the free tool Atlantis Schema Surf from Atlantis Interactive, that may be of some help to you.


2

I would actually verify how it scales up: set up a test environment, expose to to the workload you are expecting, and see what happens. That done, describing your tests would be easy. If you skip this essential step, your write-up might look good on paper and be completely useless.


2

You can do this with normal DML/DDL. Create a table with comments: mysql> create table testcomments ( -> col1 integer comment 'comment on integer column', -> col2 varchar(20) comment 'comment on varchat col'); Query OK, 0 rows affected (0.01 sec) Select the information from the data dictionary (documentation link): mysql> select ...


2

You cannot name a PRIMARY KEY. The name PRIMARY KEY is the designation of the arbitrary, or preferred, unique index (candidate key) for accessing the table. A table can have multiple unique keys, but only one PRIMARY KEY. EXAMPLE: Employee table with three unique keys EmployeeID Driver's License Number Social Security Number You can pick a name out of a ...


2

This section of documentation uses the term “User Processes” to refer to the client processes that connect to an Oracle database. If you read down the page further it describes the two types of processes that run the Oracle database server code – Server Processes and Background processes. Like DCookie I have heard Server Processes referred to as User ...


2

The most common way to do this Excel, but it has it's tradeoffs: Very difficult to update the spreadsheet if either side of the migration changes (due to missing something, something changing, etc.) Excel is harder to use by the people implement the rules documented in it. Change tracking is nearly impossible if you have multiple people collaborating on ...


2

This seems to be an elusive bug nobody wants to fix http://bugs.mysql.com/bug.php?id=24761 (2006-12-01 : Status Triaged) http://bugs.mysql.com/bug.php?id=41602 (2008-12-18 : Status Closed) http://serverfault.com/questions/235369/how-can-i-get-mysql-5-5-to-log-warnings-to-one-of-the-log-files (2011-02-14) According to ...


2

Firstly, let me say from the outset that you have my deepest sympathies! :-) Maintaining a true-to-life up-to-date schema of even a single database is bad enough, but having to maintain a single point of truth (SPOT) for a multi-database disparate data-source environment is going to be nothing short of hell on earth! You _must_ establish a SPOT for your ...


1

I would suggest to use SQL Power Doc by kendalvandyke. Its the best free tool that does a pretty decent SQL Server & Windows Documentation Using Windows PowerShell. IT is available at codeplex. SQL Power Doc is a collection of Windows PowerShell scripts and modules that discover, document, and diagnose SQL Server instances and their underlying ...



Only top voted, non community-wiki answers of a minimum length are eligible