query a new vendor database

Question

query a new vendor database

sqlfriend

SSC Guru

Points: 52769
More actions
December 18, 2019 at 5:12 pm
Go to Answer
#3708858

At work, I am often asked to do some queries for a vendor database, for example to generate some csv file and automate it to some export.
But we don't really have a data dictionary or data model of the database.
I will have to dig through the database and find relationships. Sometimes it is hard.
What is the best way and quick way to get a good model of the database so that we can write queries on it with accurate logics?

Thanks
Jeffrey Williams

SSC Guru

Points: 90244
More actions
December 18, 2019 at 7:14 pm
Answer
#3708883

The best way is to contact the vendor and request that information - hopefully they are able to provide it for you, even if it is just a subset for reporting.
Barring that - you can try using database diagrams to identify the relationships, but that requires the vendor to have setup the appropriate relationships as primary/foreign keys.
And finally - just what you are doing...but I would go a bit further and identify stored procedures, views, functions that are similar to your request and see how the vendor did it. SQL Search is a great tool for this...and one I use a lot when I have to identify relationships in a vendor supplied database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

The database diagrams feature of Management Studio may seem easy and convenient, but it does have its issues and drawbacks.

If you don't have a tool already that can "reverse engineer" a model from an existing database, one free option is ApexSQL Model:

https://blog.apexsql.com/free-sql-modeling-tool-apexsql-model-is-now-free/

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

I agree with Jeff Williams... contact the vendor for this information but be careful and don't hold your breath. They either won't have it or will be loath to part with it because it's another step to you stealing their stuff or using their stuff for things not intended. You may also be violating some usage agreement about not reverse engineering anything having to do with their product.

Using SQL Diagrams or another other "reverse engineering" tool is a great idea but... a lot of companies use code rather than DRI to validate data so that may not work correctly either.

As someone else said, you may have to continue doing thing the way you do it. You can also figure out some stuff by looking at any stored procedures, views, and functions the vendor may have provided. It's amazing what you can learn from JOINs and correlated sub-queries. Again, if the vendor wrote things into their usage agreement about not reverse engineering their stuff, you might want to keep your cards very, very close to your chest.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlfriend SSC Guru Points: 52769 More actions · Answer 3

Thanks, yes, that is mostly I did.

When I asked the database model or Relationship from the vendor, they did not reply to me. I suppose they would not like us to know more about that even we don't want to reverse engineering it but just want to query some information.