Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Data Dictionary Expand / Collapse
Author
Message
Posted Wednesday, December 11, 2013 7:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:26 PM
Points: 177, Visits: 641
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.
Post #1521907
Posted Wednesday, December 11, 2013 8:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 14,802, Visits: 27,275
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1521924
Posted Thursday, December 12, 2013 11:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:40 PM
Points: 177, Visits: 978
You can also query information_schema.columns.

select * from information_schema.columns where table_name = 'YourTableName'
Post #1522431
Posted Thursday, December 12, 2013 11:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 12,744, Visits: 31,071
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1522434
Posted Thursday, December 12, 2013 3:05 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
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?
Post #1522502
Posted Thursday, December 12, 2013 5:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:26 PM
Points: 177, Visits: 641
I am looking for technical metadata.
Post #1522534
Posted Thursday, December 12, 2013 6:09 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:03 PM
Points: 3,590, Visits: 5,098
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1522541
Posted Thursday, December 12, 2013 7:14 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:21 PM
Points: 2,842, Visits: 2,423
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)




Post #1522544
Posted Thursday, December 12, 2013 7:21 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:03 PM
Points: 3,590, Visits: 5,098
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1522546
Posted Thursday, December 12, 2013 7:25 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:03 PM
Points: 3,590, Visits: 5,098
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1522547
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse