Database Documentation

Question

Post reply

Database Documentation

HappyGeek

SSCoach

Points: 18726
More actions
October 21, 2015 at 10:34 pm

#301668

Not quite sure this is the right place for this, forgive me if I have it wrong. :blush:
To rapidly document a database I open Excel, start PowerPivot and add a connections to SQL Server with such queries as select <list> from information_schema.tables, select <list> from information_schema.columns, etc. or whatever else may be useful.
I then save these back to the Excel workbook as separate sheets (named appropriately) and save the workbook with the "Refresh on open" option set; this ensures that any new tables, columns etc. will appear in the workbook.
I have not seen any articles or documentation that suggests this is a recommended or viable method of documenting a database so wondered a) is this a bad technique to use and b) if not, although fairly simple, does it warrant an article or mention somewhere?
...

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Alexandre Araujo SSC Veteran Points: 264 More actions · Answer 1

Alexandre Araujo

SSC Veteran

Points: 264

June 15, 2016 at 4:07 pm

#1885278

what is the relevance with sql 16 ?

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 2

Alexandre Araujo (6/15/2016)
what is the relevance with sql 16 ?

I would say "None" and "Why is that important"?

Edit... ah... now I understand the question. It's because it was posted in a 2016 forum. I can't say for sure but I'm thinking that it was just because it's currently the "latest version" and I'm not sure where else someone would post something like this without having esoteric knowledge of a couple of the water-cooler threads out there.

--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)

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 3

HappyGeek (10/21/2015)
Not quite sure this is the right place for this, forgive me if I have it wrong. :blush:
To rapidly document a database I open Excel, start PowerPivot and add a connections to SQL Server with such queries as select <list> from information_schema.tables, select <list> from information_schema.columns, etc. or whatever else may be useful.
I then save these back to the Excel workbook as separate sheets (named appropriately) and save the workbook with the "Refresh on open" option set; this ensures that any new tables, columns etc. will appear in the workbook.
I have not seen any articles or documentation that suggests this is a recommended or viable method of documenting a database so wondered a) is this a bad technique to use and b) if not, although fairly simple, does it warrant an article or mention somewhere?

What do you do when you have a couple of thousand tables and a customer wants the data dictionary?

--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)

HappyGeek SSCoach Points: 18726 More actions · Answer 4

This has absolutely no relevance to SQL2016 per sa unless it happened the be the installation under consideration. I did think I put the question in the wrong place and am sorry if that offends.

As it happens there are thousands of tables and many thousands of columns. In terms of customers they would only ever be interested in a subset depending on what their role in the organisation is e.g. Accounts, Marketing, so that is precisely what they get if it is absolutely required.

Clearly Jeff is suggesting that this as a solution is non scalable and he would be absolutely correct. In a previous job the project manager had us all maintaining a separate spread sheet, that would be updated whenever a change was made; that spread sheet was the data dictionary!

...

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 5

Jeff Moden (6/16/2016)
HappyGeek (10/21/2015)
Not quite sure this is the right place for this, forgive me if I have it wrong. :blush:
To rapidly document a database I open Excel, start PowerPivot and add a connections to SQL Server with such queries as select <list> from information_schema.tables, select <list> from information_schema.columns, etc. or whatever else may be useful.
I then save these back to the Excel workbook as separate sheets (named appropriately) and save the workbook with the "Refresh on open" option set; this ensures that any new tables, columns etc. will appear in the workbook.
I have not seen any articles or documentation that suggests this is a recommended or viable method of documenting a database so wondered a) is this a bad technique to use and b) if not, although fairly simple, does it warrant an article or mention somewhere?
What do you do when you have a couple of thousand tables and a customer wants the data dictionary?

Or worse. I have a database here with nearly 30k tables and over 20k views...

That reminds me - I really ought to point Redgate's SQLDoc at it. Or at least at the Dev version of that database...

Thomas Rushton
blog: https://thelonedba.wordpress.com

Brandie Tarvin SSC Guru Points: 173003 More actions · Answer 6

The last data dictionary I did was an SSRS report that documented not only the tables and columns, but the dependencies and metadata descriptions (for those objects that had it). The parameters of the report allowed the users to choose a specific table name or a specific column name (to see what tables had a column by that name) or just choose everything.

Totally biased opinion here, of course, since I created this as my first ever DBA project, but maybe look into doing something like that. That way the database is documented (and you can include data types & sizes) and scalable and searchable through easy means.

EDIT: Plus, of course, once you design the code behind the report, you never have to update the report itself when tables are added / deleted / modified. The report will be dynamic enough to change on its own since you're using either catalog views or INFORMATION_SCHEMA tables.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 7

HappyGeek (6/16/2016)
This has absolutely no relevance to SQL2016 per sa unless it happened the be the installation under consideration. I did think I put the question in the wrong place and am sorry if that offends.

Nah... you just posted it in the "latest version" forum. I get that. Not sure where else you would have posted it and no offense taken.

This actually sounds like it could be a great article, especially if you "set expectations" at the beginning for some of the questions that you know will pop up thanks to some of the responses on this thread.

I'd also explain that, although the methods you used were specifically meant to be used for your data dictionary, they could easily be used for other things and I'd really try to drive that point home in the front matter (Introduction) of the article.

With those expectations in mind, I think it would be an awesome article and you should definitely go for it.

--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)

Brandie Tarvin SSC Guru Points: 173003 More actions · Answer 8

Jeff Moden (6/16/2016)
I think it would be an awesome article and you should definitely go for it.

+1

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 9

ThomasRushton (6/16/2016)
Jeff Moden (6/16/2016)
HappyGeek (10/21/2015)
Not quite sure this is the right place for this, forgive me if I have it wrong. :blush:
To rapidly document a database I open Excel, start PowerPivot and add a connections to SQL Server with such queries as select <list> from information_schema.tables, select <list> from information_schema.columns, etc. or whatever else may be useful.
I then save these back to the Excel workbook as separate sheets (named appropriately) and save the workbook with the "Refresh on open" option set; this ensures that any new tables, columns etc. will appear in the workbook.
I have not seen any articles or documentation that suggests this is a recommended or viable method of documenting a database so wondered a) is this a bad technique to use and b) if not, although fairly simple, does it warrant an article or mention somewhere?
What do you do when you have a couple of thousand tables and a customer wants the data dictionary?
Or worse. I have a database here with nearly 30k tables and over 20k views...
That reminds me - I really ought to point Redgate's SQLDoc at it. Or at least at the Dev version of that database...

BWAA-HAAA!!!! You're right... that is "worse". 😀 Man, that's a lot of tables and views for a single database.

--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)

mpaine Grasshopper Points: 13 More actions · Answer 10

Our team uses extended properties which has the nice benefit of TFS compatibility. There we can store Description, ETLUpdateStrategy, Notes, SSIPackage and other meta data.

Eric M Russell SSC Guru Points: 125524 More actions · Answer 11

Jeff Moden (6/16/2016)
HappyGeek (10/21/2015)
Not quite sure this is the right place for this, forgive me if I have it wrong. :blush:
To rapidly document a database I open Excel, start PowerPivot and add a connections to SQL Server with such queries as select <list> from information_schema.tables, select <list> from information_schema.columns, etc. or whatever else may be useful.
I then save these back to the Excel workbook as separate sheets (named appropriately) and save the workbook with the "Refresh on open" option set; this ensures that any new tables, columns etc. will appear in the workbook.
I have not seen any articles or documentation that suggests this is a recommended or viable method of documenting a database so wondered a) is this a bad technique to use and b) if not, although fairly simple, does it warrant an article or mention somewhere?
What do you do when you have a couple of thousand tables and a customer wants the data dictionary?

There are stored proceures in the public domain that will query INFORMATION_SCHEMA and sys tables and spit out a simplified user friendly DDL schema for all databases and all user objects, including modified date, permissions, dependencies, and last known date the object was referenced (based on operational stats dmv cache). Wrap an SSRS report and SharePoint page around it, let them point it to any environment, and you can eliminate a lot of drive by questions from IT.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

higgim SSCarpal Tunnel Points: 4090 More actions · Answer 12

I'm a big fan of Redgate SQL Doc if that is any good for you?

http://www.red-gate.com/products/sql-development/sql-doc/