General discussion


Building a data values dictionary

By MaryWeilage Editor ·
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:

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

consider: table ownership, syscolumns, and description

by doug.gelling In reply to Building a data values di ...

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.

Collapse -

Adding Owner and Description

by artful In reply to consider: table ownership ...

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

Collapse -

column description

by doug.gelling In reply to Adding Owner and Descript ...

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.

Collapse -

What next?

by james.ruzicka In reply to Building a data values di ...

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

Collapse -

What To Do with the Data Values Dictionary

by artful In reply to What next?

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.


Collapse -

better way to handle embedded quotes

by mmcmanus In reply to Building a data values di ...

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

Collapse -

Better Way to Handle Quotes

by artful In reply to better way to handle embe ...

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


Collapse -

A variation of the same theme

by iam01iam01 In reply to Building a data values di ...

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 (

Related Discussions

Related Forums