Detailing the Data Architecture

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Detailing the Data Architecture

Author

Topic

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-03-05 : 06:30:55

Hi Guys,

I've been tasked to document the data architecture for a database which current doesn't have any documentation at all.

As a Junior DBA i've never had to do anything like this kind of task and just wondered if you guys could give any tips or idea where I should look into.

My first intentions currently are

To list all tables in the database, what type of information is held in the tables, PK's and FK's on each table (this is to start with)

All views and Stored Procs

then Going deeper look at the relationship for each of the tables and any constraints other that foreign keys are on each table i.e. check, defaults

Then what infomation is held in each view and what tables are used, and also what task each stored procedure carries out

as this is a big project any slight advice would be a big help

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-03-05 : 08:28:12

Anyone or are my ideas ok for a starting point

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-03-05 : 08:35:49

That's a pretty formidable task, but here are a few starting points

select * from information_schema.columns
select * from information_schema.key_column_usage
select * from information_schema.table_constraints

and a few others in system views for you database(s). There is also software available to help you diagram out the relationships between the tables.

Jim

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-03-05 : 10:15:51

Excellent Jim, Thanks for the advice i was starting to look at the information_schema views to try and get a type of hierachy Table>Columns>Keys>Constraints

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-03-05 : 10:57:28

This will help, then
select * from sys.foreign_key_columns

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources