SQL Database Documentation
-
We have a situation where the Lead SQL Developer is leaving the company and he says there's no documentation on the database structure, or what reports use which tables, queries, stored procs, functions etc. What is the best way to document table relationships in SQL so we are not in this position again in the future?
I'm starting with Visio entity diagram, also using Excel. However, any software you use to best documents reports and the tables, procs, etc. used for certain processes, would be helpful.
Thank you for your advice.
Charles P -
Hi Charles,
I can think of the following options:
1. Database diagrams - could be useful to find out primary-key foreign key relationships between the tables,
2. You can also use the sql_dependencies view as:
SELECT DISTINCT OBJECT_NAME(object_id) AS ReferencingObject, OBJECT_NAME(referenced_major_id) AS ReferredObject FROM sys.sql_dependenciesThe second option will give you the list of stored procedures / functions that are using certain tables etc.
Thanks.
- Charles_P - Sunday, August 5, 2018 8:39 PM
Have you taken the time to look at any of the stored procedures, functions, and views for embedded documentation? If there's any, that's where you find what you won't find anywhere else except maybe in any documentation that might be in the extended properties of the objects. Ah... one more place. Documentation in the SQL Agent Jobs, themselves or maybe even in the ticketing system that keeps track of requests and where the source code for the changes are stored. Also, did you folks use the idea of requirements documents followed with function design documents?
Do any of the other developers have the essential tribal knowledge to carry on?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you both. Good advice. I tried the database diagram and it is very useful. I'm also starting to look at any documentation inside of the stored procedure, functions and views.
Thanks,
Charles P -
Charles,
Try the following link on an artical about retreiving the database METADATA: https://dzone.com/articles/retrieving-table-metadata-from-sql-server-catalog
The artical explains with SQL Scripts to pull the information you are looking for that is "user" freindly. Another way is to use the Script database task, this will create all the nessicary code to rebuild a clean version of the database with:
* Tables
* Views
* Primary Keys
* Foreign Keys
* Indexes
* Triggers (Including Code)
* Check Clauses (Including Code)
* Stored Procedures·
Tim Morehead -
Tim,
I don't know about anyone else but that came out looking like a very strange language. Not sure what font you uses but there's a one to one character match.Here's what Tim posted in a more readable fashion...
_________________________________________________________________________Charles,
Try the following link on an artical about retreiving the database METADATA: https://dzone.com/articles/retrieving-table-metadata-from-sql-server-catalog
The artical explains with SQL Scripts to pull the information you are looking for that is "user" freindly. Another way is to use the Script database task, this will create all the nessicary code to rebuild a clean version of the database with:
* Tables
* Views
* Primary Keys
* Foreign Keys
* Indexes
* Triggers (Including Code)
* Check Clauses (Including Code)
* Stored Procedures·--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Charles_P - Monday, August 6, 2018 9:21 PM
I'm hoping that someone actually enforced some standards for commenting for you and that they turn out decent. It's a tough row to hoe when you lose key people.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Timmorehead 14652 - Monday, August 6, 2018 10:23 PM
I'll just say "It's all Greek to me..."...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Look at SQL Tool belt by Red Gate, the sponsors of this site. some great documentation tooling, source code control and migration scripting functions. Apex and Pragmatic Works (now part of Sentry One) also do database documentation tools. I think Apex can also document SSRS reports and SSIS packages.
No affiliation, just a satisfied customer.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply