Data Dictionary

Question

Data Dictionary

sql_novice_2007

Hall of Fame

Points: 3307
More actions
December 11, 2013 at 7:42 am

#395971

Hi,
I need to get the data dictionary for the database tables that are related to users and orders(Users table and its related tables,order table and its related tables).
I got all the tables that are related to user and order table. How will I get the data dictionary for those objects?
Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

There isn't a single definition for what exactly is meant by "data dictionary."

Do you mean the table definitions, indexes, constraints, all as defined by T-SQL? Or are we talking about something else?

If we're talking the T-SQL definitions, you could try using the Generate and Publish Scripts wizard within SSMS. Right click on your database and select Tasks then Generate Scripts. The rest should be fairly self-explanatory.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 2

You can also query information_schema.columns.

select * from information_schema.columns where table_name = 'YourTableName'

Lowell SSC Guru Points: 323504 More actions · Answer 3

i've heard the data dictionary as being the Extended Properties comments that are potentially included in a database to describe objects/tables /columns, but only if someone has gone to the trouble of using a tool to put comments in via the sp_addextendedproperty procedure

does this return anything in your database?

select * from sys.extended_properties

SELECT *

FROM fn_listextendedproperty (NULL, NULL, NULL, NULL, NULL, NULL, NULL);

--all procedures

SELECT objtype, objname, name, value

FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'PROCEDURE', NULL, NULL, NULL);

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sneumersky SSCertifiable Points: 7667 More actions · Answer 4

Are you asking for:

1. Business Metadata (What does the data in this table/column mean in business terms) or

2. Technical Metadata (data length, data type, etc) or

3. Process Metadata (how often is this table loaded, what is the most recent load date for this table)?

Is this for an operational/transactional system, or is this more for a reporting/DWH/BI Solution?

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 5

sql_novice_2007

Hall of Fame

Points: 3307

December 12, 2013 at 5:26 pm

#1673131

I am looking for technical metadata.

Dwain Camps SSC Guru Points: 86908 More actions · Answer 6

jshahan (12/12/2013)
You can also query information_schema.columns.
select * from information_schema.columns where table_name = 'YourTableName'

Better to use sys.columns or sys.all_columns as information_schema.columns is being deprecated.

All the technical metadata exists in there. Now extracting and formatting it is the only issue and there is no tool in SSMS for this. I have built one (it even allows you to store table/column names as extended properties) but unfortunately it cannot be shared (proprietary).

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

happycat59 One Orange Chip Points: 29386 More actions · Answer 7

dwain.c (12/12/2013)
jshahan (12/12/2013)
You can also query information_schema.columns.
select * from information_schema.columns where table_name = 'YourTableName'
Better to use sys.columns or sys.all_columns as information_schema.columns is being deprecated.
All the technical metadata exists in there. Now extracting and formatting it is the only issue and there is no tool in SSMS for this. I have built one (it even allows you to store table/column names as extended properties) but unfortunately it cannot be shared (proprietary).

Are you sure about INFORMATION_SCHEMA.Columns being deprecated...it is a SQL-92 ANSI standard view. I don't think it will be disappearing anytime soon. Maybe you were thinking or syscolumns (which I think is to be deprecated at some point in the future)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 8

happycat59 (12/12/2013)
dwain.c (12/12/2013)
jshahan (12/12/2013)
You can also query information_schema.columns.
select * from information_schema.columns where table_name = 'YourTableName'
Better to use sys.columns or sys.all_columns as information_schema.columns is being deprecated.
All the technical metadata exists in there. Now extracting and formatting it is the only issue and there is no tool in SSMS for this. I have built one (it even allows you to store table/column names as extended properties) but unfortunately it cannot be shared (proprietary).
Are you sure about INFORMATION_SCHEMA.Columns being deprecated...it is a SQL-92 ANSI standard view. I don't think it will be disappearing anytime soon. Maybe you were thinking or syscolumns (which I think is to be deprecated at some point in the future)

Hmmm... now I am not sure. I thought I'd heard that but I could be wrong.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Dwain Camps SSC Guru Points: 86908 More actions · Answer 9

Perhaps it is just a recommendation:

We recommend that you use catalog views because they are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information. All user-available catalog metadata is exposed through catalog views.

http://technet.microsoft.com/en-us/library/ms174365.aspx

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

sneumersky SSCertifiable Points: 7667 More actions · Answer 10

All solutions presented are acceptable and contain technical metadata.

If you want "quick and dirty" and need to port code to another RDBMS someday, then I would use INFORMATION_SCHEMA.COLUMNS as it is ANSI SQL Compliant.

If you are not porting, sys.columns....sys.all_columns are also ok.

If you are including a data dictionary as part of an enterprise wide metadata integration strategy, then extended properties can be used to enrich the data dictionary with business metadata and some types of process metadata.

Steve Neumersky