data dictionary tool

Question

data dictionary tool

Ramu Poonjolai

SSC-Addicted

Points: 451
More actions
December 11, 2007 at 2:20 pm

#154014

Is there any tool for data dictionary? Basically there are no
descriptions/comments to many of the columns (about 99%) in the database.
Datawarehouse team does not know exactly what those columns are, where its
used, how its used. No comments have been defined in the system tables. We
would like to have a tool where SME's/Bussiness analysts enter the
description for the columns in the production database. We have SQL Server
databases, many oracle dbs, few sybase databases, ACCESS. Sometimes we might
have to search for strings in the descriptions entered.

Viewing 15 posts - 1 through 15 (of 23 total)

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

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 1

try searching the web for metadata repository. there are third party products des8igned to do exactly what you are looking for, plus more, such as data lineage. we started our specifically for our data warehouse.

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

Heh... output the table names and column names to a spreadsheet... have them fill out the spreadsheet. Use the spreadsheet as the source of data to insert the comments in the metadata.

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

Steve Jones - SSC Editor SSC Guru Points: 736188 More actions · Answer 3

I'm with Jeff. It's hard to maintain this and not necessarily any easier with an application writing to the tables than an XLS. Give the person with responsibility for deploying changes to the tables the responsibility for updating the XLS.

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 4

As I'm sure you know, all SQL Server Objects support extended properties; I try (with only modest success) to define and populate a "description" property (and others as desired) to provide some level of documentation comments.

The extended properties are scriptable, and It should not be too difficult to build some scripts to populate things like column definitions, and generate documentation dumps.

(sp_[add|drop|update]extendedproperty; listextendedproperty)

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 5

Jim Russell (12/13/2007)
As I'm sure you know, all SQL Server Objects support extended properties; I try (with only modest success) to define and populate a "description" property (and others as desired) to provide some level of documentation comments.
The extended properties are scriptable, and It should not be too difficult to build some scripts to populate things like column definitions, and generate documentation dumps.
(sp_[add|drop|update]extendedproperty; listextendedproperty)

Exactly what I've done!

I found that you can even add extended properties to the columns returned by a table-valued function though SSMS (as of 2K5) won't even display the column names! (...unless someone can tell be how to add this to the object browser view since it's a simple query to get the information?)

select o.name as 'function', c.name as 'column', e.name, e.value

from sys.all_objects o

join sys.all_columns c on o.object_id=c.object_id

cross apply fn_listextendedproperty(NULL,'SCHEMA','dbo','Function',o.name,'Column',c.name) e

where o.type in ('IF','TF')

order by o.name,c.name

Derek

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 6

Very nice Derek!

Getting the list might be a good use of the custom reports option, although I don't recall if the report gets any indication of the object from which it was called. You are right, it would be nice if the columns were reported by the Object Explorer for both table returning UDFs and stored procedures.

David Davis-459893 SSC Journeyman Points: 99 More actions · Answer 7

We created an Access database for the Data Governance(DG) team to provide and track definitions. Data Governance is the business assigned SMEs - each has their own subject area they are responsible for.

This allows us to track, support and report on the progress of the metadata definitions w/o spending a lot of time.

On a regular basis, or before a release, I review the definitions, ensure the GUI terms are defined (if not I follow up with the DG team, get the definitions updated then promote the metadata to a production SQL table which is accessed by a report call from ProClarity - this provides users with the ability to see the definitions, algorithms and sourcing of the data in their queries and reports.

Robert Bedick Old Hand Points: 310 More actions · Answer 8

SchemaToDoc (http://www.schematodoc.com) has an interface that lets you easily enter descriptions for tables, fields, and views. It stores the descriptions in your database as extended properties. You can then include these descriptions in an output file (Word or HTML) that describes your database's metadata.

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 9

Also worth a look is RedGate's SQL Doc (http://www.red-gate.com/products/SQL_Doc/index.htm)

Tommy

Follow @sqlscribe

magickrosian SSC Rookie Points: 40 More actions · Answer 10

hi everybody!

Thank you for sharing those 2 softwares, i was looking for them.

but they seemed to be made for DBAs, and i'd like that kind of software for end users...

any ideas?

i mean like a software which would get the data (values, types, descriptions) from the repository and show them in a better look for the end users...

Steve Jones - SSC Editor SSC Guru Points: 736188 More actions · Answer 11

I don't know of any software like this for end -users. Many packages will export to Word or HTML, so maybe you can keep a copy in one of those formats around for people to use.

Ramu Poonjolai SSC-Addicted Points: 451 More actions · Answer 12

there are tools like http://www.datadictionary.com

good tool for end users.

magickrosian SSC Rookie Points: 40 More actions · Answer 13

hello everybody,

I just remember there is a data dictionnary with oracle. With it you can make queries to list all your tables, their descriptions... and so on... but is there the same system with SQL server?

I guess it would have the same kind of query system for metadata, but i'm not too familiar with sql server

thanks

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

I'm thinking that there's a lot of people that don't understand what a good data dictionary is nor how it's made. Regardless of the tool involved, without intelligent and thoughtful human intervention, the data dictionary is nothing more than a useless regurgitation of what the meta-data is.

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