November 18, 2016 at 4:19 am
Hi All,
A customer of mine has huge number of sql 2012 databases but he doesnt know which data sits where. he needs me to help me in creating some sort of data relationship mapping diagram or some sort of architecture.
Any idea how this could be approached? or can I make use of a tool to get this done.
November 18, 2016 at 4:32 am
Also, the end requirement is to create a database map that can be used for future builds and enhancements.
Which are the best tools to do database mapping>?
November 18, 2016 at 4:43 am
Benki Chendu (11/18/2016)
Hi All,A customer of mine has huge number of sql 2012 databases but he doesnt know which data sits where. he needs me to help me in creating some sort of data relationship mapping diagram or some sort of architecture.
Any idea how this could be approached? or can I make use of a tool to get this done.
I have done such unraveling in the past, the task can range from few days to months, depending on the total complexity, logic distribution etc. It certainly warrants the purchase of a good ERD and DB Documentation tools, will save a lot of time.
😎
November 18, 2016 at 5:49 am
Eirikur Eiriksson (11/18/2016)
Benki Chendu (11/18/2016)
Hi All,A customer of mine has huge number of sql 2012 databases but he doesnt know which data sits where. he needs me to help me in creating some sort of data relationship mapping diagram or some sort of architecture.
Any idea how this could be approached? or can I make use of a tool to get this done.
I have done such unraveling in the past, the task can range from few days to months, depending on the total complexity, logic distribution etc. It certainly warrants the purchase of a good ERD and DB Documentation tools, will save a lot of time.
😎
Any suggestions?
November 18, 2016 at 5:55 am
Two tools that I would suggest that can help with this (you're still going to have to do a lot of work to understand exactly what is being documented) are Embarcadero ER/Studio and Redgate SQL Doc[/url]. You might also want to look at Redgate SQL Dependency Tracker (that one may or may not be as helpful as the others).
DISCLOSURE: I work for Redgate.
"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
November 18, 2016 at 6:03 am
Benki Chendu (11/18/2016)
Eirikur Eiriksson (11/18/2016)
Benki Chendu (11/18/2016)
Hi All,A customer of mine has huge number of sql 2012 databases but he doesnt know which data sits where. he needs me to help me in creating some sort of data relationship mapping diagram or some sort of architecture.
Any idea how this could be approached? or can I make use of a tool to get this done.
I have done such unraveling in the past, the task can range from few days to months, depending on the total complexity, logic distribution etc. It certainly warrants the purchase of a good ERD and DB Documentation tools, will save a lot of time.
😎
Any suggestions?
I use Redgate SQL Doc for documentation and variety of ERD tools depending on the task, Embarcadero ER/Studio is good, Apex has SQL and BI documentation package two which is good, Toad is always classic and for simplicity sake, yEd and vim.
😎
November 18, 2016 at 11:52 am
As a start, you can combine sp_MsForEachDB with a query against INFORMATION_SCHEMA or SYS views.
For example:
-- Search for all stored procedures and views that include a keyword in their definition:
exec sp_msforeachdb
'
use ?;
select db_name() as db_name, type, name as object_name
, object_definition(object_id) as object_text
from sys.objects as o
where is_ms_shipped = 0
and object_definition(object_id) like ''%MyTable%'';
';
-- List a data dictionary of all tables and columns:
exec sp_msforeachdb
'
use ?;
select db_name() as db_name, *
from information_schema.columns
order by table_name, column_name;
';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply