SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to study the layout of a database?


How to study the layout of a database?

Author
Message
primitivefuture2006
primitivefuture2006
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 144

Hello friends,

Suppose you download a sample database, whether it be AdventureWorks, Northwind, or whatever other database. What steps do you take to thoroughly understand the layout of the database?

Do you use Excel or some software to note all the table names, columns, and keys? Do you use some tool to draw a Entity Relationship Diagram?

I ask this because if I were to do joins or subqueries, I need to know all the relevant tables, columns, and keys to write the query. What would you do to learn the database layout fully to perform such queries?

Thanks for all the advice!


ZZartin
ZZartin
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26738 Visits: 17644
The first step, you pray that whoever built it used somewhat intuitive names for tables columns and constraints.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221176 Visits: 25130
ZZartin - Tuesday, September 18, 2018 9:05 AM
The first step, you pray that whoever built it used somewhat intuitive names for tables columns and constraints.

Assuming there are some constraints. And that the tables are not all heaps.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
primitivefuture2006
primitivefuture2006
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 144
Please. I am a noob but I really want to learn. Sorry if my question is funny, but I really would appreciate real advice. I am dreaming of landing a new career
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221176 Visits: 25130
primitivefuture2006 - Tuesday, September 18, 2018 6:14 PM
Please. I am a noob but I really want to learn. Sorry if my question is funny, but I really would appreciate real advice. I am dreaming of landing a new career

Your question is not 'funny', don't worry! But it is difficult to answer, because the answer will vary from one database to the next.

A database can have a huge number of moving parts, and not all of them need to be contained within the database itself (SSIS packages, external Windows and Web apps, external scripts and other things can all affect the state of a database). Trying to get to grips with the entire scope of a database and its connected applications is likely to be a big task. I've seen applications where all of the data manipulation code was held outside of the database, in a desktop app, perhaps. Trying to understand 'the layout of the database' in such situations is difficult, because the code is largely hidden (unless you are also an Windows application developer, in which case you may be able to dig into the source code and work things out).

Having said that, if a database is serving a single application and has been built based on the principle of having as much code as possible within the database, the task gets easier. If the database objects also follow strict naming conventions and have been built by a diligent development team, you should be able to get somewhere.

One thing you can do is to create one or more database diagrams (use the 'Database Diagrams' node in SSMS). These will provide a graphical display of relationships between tables (assuming that those relationships have been created in T-SQL).

Beyond that, start looking at the design of some of the tables, views and stored procs to attempt to get a feel for what is going on and how things have been put together.

Don't be daunted if you feel like you are not getting anywhere fast. This is a task which even very experienced developers find difficult.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
jonathan.crawford
jonathan.crawford
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5194 Visits: 1467
Start by understanding what business solution the database is providing. Are you looking at a way to track widgets being built? Healthcare? Financial transactions? Customer Service?

That will help you have some sense of how the data flows through the system. If you can have someone show you a front end, sit down with them for that. While they're showing you, write up statements you can reference later looking for referential integrity (customers have orders, create contact records, each contact is tracked but can be passed to multiple departments, etc.)

Then hope names make sense. Many times there are reference tables with the front end descriptions for things that are only referenced by key values in the tables. If you find one that you want to track, find every one of the columns named like that key, and/or find foreign key constraints.

Look at index usage, query store, any other tools within the database that are built to show you where the data is being used.
Views, stored procedures, reporting from the database are all good places to look to find out how it works.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
pietlinden
pietlinden
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54578 Visits: 17300
ZZartin - Tuesday, September 18, 2018 9:05 AM
The first step, you pray that whoever built it used somewhat intuitive names for tables columns and constraints.

And didn't try to punk you by using godawful Hungarian notation and all lowercase like that person thinks he's e.e. cummings or something.

VoldemarG
VoldemarG
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2465 Visits: 735
I would say this question is kind of difficult not just because it varies from database to database and environment and environment but also from person to person and the particular goals that you set.
For example, depending whether you want to see a big picture of how data is organized within normalized or de-normalized tables or study what a particular t-sql stored proc or function does, or how a complex-looking view pulls data from many tables and other views, - you may want to see and ER diagram (ER = Entity Relationships). Of course if database is huge and has 500 or 2000 tables in it your ER diagram will be the size of a building and useless for your review Smile. I usually do ER-Diagramming of a particular subject area that I am interested in that involves a dozen or less of core tables and views containing the data that I need to learn and understand. You can do such diagramming using a variety of tools, including the one built-into SQL Server (not really good and flexible...) or something more sophisticated like ERwin or MS Visio.

Database Documentation can be of paramount importance and use but it is usually a rare luxury in most IT shops.
Using an example of AdventureWorks database that you mentioned, here is what good documentation for it looks like (PDF file):
http://allstarsql.com/adventureworks2012.pdf

It never hurts to ask your DBA for such documentation Smile. At least you can ask. One shop out of 100 will have such documentation, you may just get lucky... Cool

I also do this for analyzing stored procedures or complex views: finding dependencies. I.e., objects that the stored proc depends on (uses in its code) and/or objects that use the object that I am analyzing. Since the built-in Sp_depends is not really dependable for such purposes, a third party tool may be recommended such as Redgate Dependency Tracker.

Some SQL Server undocumented stored procedures showing up and down level dependencies can be useful too:
-- Value 1053183 shows objects that the specified object is dependent on
EXEC sp_MSdependencies 'vw_MyComplexHeadacheView', null, 1053183

-- Value 131527 shows objects that are dependent on the specified object
EXEC sp_MSdependencies 'vw_MyComplexHeadacheView', null, 1315327



Voldemar
likes to play chess
jonas.gunnarsson 52434
jonas.gunnarsson 52434
SSC Eights!
SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)

Group: General Forum Members
Points: 820 Visits: 302
No, it's a good question.

You could look after:
  • If there are som diagrams under the 'Database Diagrams' node in SSMS
  • If tables and columns have Extended Properties.

There are several programs that extract the documentation in the database(if there are some).
To get a simple list over the documentation of tables and columns:

select object_schema_name(ep.major_id) [Schema],
object_name(ep.major_id) TableName,
c.column_id ColumnId,
c.name ColumnName,
ep.value [Description]
from sys.extended_properties as ep
inner join sys.sysobjects as so on so.id = ep.major_id
left outer join sys.columns as c on c.object_id = ep.major_id and c.column_id = ep.minor_id
where so."type" = 'U' and so.name <> 'sysdiagrams' and ep.class_desc = 'object_or_column' and ep.name = 'MS_Description'
order by object_schema_name(ep.major_id), object_name(ep.major_id);

paulsbuxton
paulsbuxton
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 61
Database Diagrams, but I find this quite clunky with a bigger DB.

Numerous bits of software out there to do the job for you. I've found myself using the latest build of SchemaSpy actually because its quite easy to read what is generated.. but If you want to give people access to the schema within an org, I've had great success with it - for multiple backends... You will need Java, and you will need the MS JDBC drivers....

http://schemaspy.org/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search