Tool that identifies a sql database schema

From time to time we are requested to pull information from various sql databases and the challenge is always the same - the relationships between tables.

In such cases, Is there a tool available that will print a schema on a database?
SpaceCoastLifeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
Well, tables are part of (belong to) a schema, and schema's are part of (belong to) the database.

To see all the objects (well, tables at least) there are system vies within the database that can be used.

Is that the type of thing that would help your cause ?

For example, an easy way to start with is :
select * from INFORMATION_SCHEMA.tables

Open in new window

or more of a database diagram ?
SpaceCoastLifeAuthor Commented:
That doesn't give me the relationships between tables. The objective is to create queries returning specific data from multiple tables
nociSoftware EngineerCommented:
AFAICT there is never such data in the tables. Except for consistent naming schemes.
Views will give some insight if declared.  
Stored procedures might be useful.
Otherwise you will need to log the queries fired at the database and find the relations from that.

Indexes might yield some possible insight on often searched for items. Which might be part of a relation.
SolarWinds® VoIP and Network Quality Manager(VNQM)

WAN and VoIP monitoring tools that can help with troubleshooting via an intuitive web interface. Review quality of service data, including jitter, latency, packet loss, and MOS. Troubleshoot call performance and correlate call issues with WAN performance for Cisco and Avaya calls

behdad khoshbinCommented:
In our weblog we use code smith, it has plenty of templates that you can use
Mark WillsTopic AdvisorCommented:
There are tools available (some free, better ones cost $), and Visio Pro is a good tool to use - but it does cost - https://support.office.com/en-us/article/Reverse-engineer-an-existing-database-into-a-database-model-fb034862-acfc-45bc-88b2-f33d1e1f8614#ID0EAABAAA=Visio_Pro_for_Office_365

There are the relationships with SQL as well - for example, to show foreign key constraints (dependency)
SELECT 
    C.CONSTRAINT_NAME [constraint_name] 
   ,C.TABLE_NAME [referencing_table_name]                -- table name that has to check another table
   ,KCU.COLUMN_NAME [referencing_column_name]            -- column that will be used to check (must exist) in the other table
   ,C2.TABLE_NAME [referenced_table_name]                -- table being checked for the value to exist
   ,KCU2.COLUMN_NAME [referenced_column_name]            -- column of the above table which must have the existing value
   ,C.TABLE_NAME+'.'+KCU.COLUMN_NAME+' must exist in '+C2.TABLE_NAME+'.'+KCU2.COLUMN_NAME as Conditions
   ,RC.DELETE_RULE delete_referential_action_desc 
   ,RC.UPDATE_RULE update_referential_action_desc

FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS C 
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME 
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2 ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME 
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION 
WHERE  C.CONSTRAINT_TYPE = 'FOREIGN KEY' and c.TABLE_NAME like 'EE_MW%'
ORDER BY C.CONSTRAINT_NAME

Open in new window

Otherwise you will need to find a tool that can reverse engineer a database diagram for an existing DB.
SpaceCoastLifeAuthor Commented:
Given my need to construct queries with multiple tables and no idea which tables to use or fields to join then together, do you have any recommendations on 3rd party reverse engineering products? I'm not opposed to buying for the right solution.
nociSoftware EngineerCommented:
toad  is a capable tool for reporting on / auditing of databases.
https://www.quest.com/toad/
SpaceCoastLifeAuthor Commented:
I installed Toad but quite honestly, I have no idea what to do next that will give me insight on query construction within a selected database.

It looks identical to SQL Server Management Studio which I obviously already have.

Are you in a position to point me to a "Starting Point"?
nociSoftware EngineerCommented:
A coworker uses it a lot  alas he is on holiday now.   (i do networks & systems management).
It should be able to load a schema from the database and anlyse tables there.
(It wil not "auto" figure itself  where relations are, but it  should be able to analyse queries done on the database.
and learn from there index usage, cache usage etc. ).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SpaceCoastLifeAuthor Commented:
Links to how using Datamodeler very helpful
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.