Discussion Locked

Building a data values dictionary

0 helpful
8 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

This week's SQL Server newsletter presents the basic SQL for building a data values dictionary.

Will you follow the steps in this tip to create a data values dictionary? Do any of your clients ever request this kind of data? Please let us know.

If you aren't subscribed to the free SQL Server newsletter, click the following link to automatically sign up:
http://nl.com.com/MiniFormHandler?brand=builder&subs_channel=bldr_front_door&list_id=e046&tag=fb

8 replies

by doug.gelling

1. This proc / data model does not account for 2 owners having a table with the same name. The owner or uid should be required column of the table and a parameter to the proc. If the owner parameter is not provided, the value might be defaulted based on who is executing the proc, but defaulting it could have drawbacks.

2. I would develop the proc to get the retrieve the datatype from the syscolumns table. Why have the user provide something that is already available?

3. In my own implementation, I would require a definition be provided for the column. If you're going to go through this much effort, you might as well have the user enter a text description to enahnce the data dictionary.

Finally, I'd make sure the column existed in the table before inserting into the data dictionary. That is probably understood and left out of the example for brevity's sake.

by artful

Good points, all. Regarding the Owner issue, I simply forgot to ancticipate this. As for checking the column's existence and data type, I wanted to keep the code as brief as possible, and assumed it would be the developer who was running it. Finally, as for the column's description, I can see that both ways. As you point out, it's easy enough to get this information from syscolumns if you want to add it.

Thanks for reminding me about the Owner issue. (It never comes up in my typical apps, and I had forgotten about it.)

by doug.gelling

OK, I can accept all that. But if you plan to run reports for end users, having the column description would be a very nice feature.

by james.ruzicka

Okay, so you have this data values dictionary. What are some of the things we can do with it? Are their any examples available?

by artful

One thing you could do, in just a few minutes, is create a report for the various stakeholders. Using Access or Crystal or Reporting Services, this would take only a few minutes.

With a little more work, you might enhance said report to include statistics for each entry on percentage of entries.

Arthur

by mmcmanus

Instead of using the confusing triple quotes, I like this better:

set @s = 'select distinct ' + char(39) + @TableName + char(39) + ', '+ char(39) + @ColumnName
+ Char(39) + ',' + char(39) + @DataType + char(39) + ', ' + @ColumnName + ' from ' + @TableName

by artful

You're quite right. Your way ends up doing the same thing, but is much more readable. Thanks!

Arthur

by iam01iam01

I've used a similar approach to analyse data quality issues in a database. My suggestion is to replace the "distict" with something like:
select col1, count(*)
from tab1
group by col1;

So you collect not only the values, but also how frequently each value is used.

Ismael Matos (www.g-day.info)

This discussion is closed to new replies.