How do i get the SQL-Tables behind CRM-Queries?

(0) Share

Report

Posted on by Community Member

Hi dear community,

we have a Microsoft Dynamics CRM running in which a lot of data is stored and queried.

Now I would like to export only certain tables from my SQL database (the ones I need for certain queries/forms).

Example: There is a query/form called "monthly report" in which a table with different kinds of data are shown (in the CRM).

How do I find out which views (and then tables) of my SQL server are used to supply this query/form with data?

Daniel

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (1)

Suggested answer

hellosuresh 539 on at

Like (0)

Report

Hi Daniel,

If you are using an entity named "monthly report", then you can find a table based on CRM Version. CRM 2013 and above you can find table called "monthly report". If version of CRM is 2011, then you will notice two tables one is "monthlyreport", and second one is "monthlyreportbase". You can also find filtered view by name "Filteredmonthlyreport", which is used to query based on user who logged in CRM.

If you know tool called sql profiler which resides in SQL Server Management Studio, by using this you can know what views/tables are called by your CRM.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Unfortunately there is neither a view nor a table called "monthly report".

When I look at the CRM's UI, on the top left it says "Microsoft Dynamics CRM", then there is a little house symbol next to it. Next to this symbol, I can choose a category and next to this, I can choose another category.

When I choose both categories, on the main part of the screen, I can choose a table to be shown. This is the table/query/view I am talking about. This is the one I need to save (and for that I need the tables on the SQL server).

Thanks for responding so quickly.

Was this reply helpful? Yes No
hellosuresh 539 on at

Like (0)

Report

The one you mentioned is View. On top of the table (View), you can see the view name as list.

That is just a CRM View or Dashboard contains view. That view belongs to on entity. If you are a system Administrator, in Ribbon buttons (below blue bar) you can see three ellipses. when you click on that you can see an option named "Customize Entity".

From there you can know this view belongs with entity.

If possible share screen shot, so that we can better help.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

And how do I know which SQL tables (on the server) are "connected" to this entity?

So if monthly reports belong to an entity called ABC, how do I know which tables hold the information of ABC?

Was this reply helpful? Yes No
Verified answer

Wayne Walton 13,730 on at

Like (0)

Report

If you go to Settings -> Administration -> Customization -> Customize the System, you will see a list of all entities and their friendly names. If you click on an entity name, you'll get a form showing the properties of the entity. There is a read-only field called "name" that is your schema name, which also is your database table name.

Most of them are pretty straightforward, like Accounts are literally accounts. but if you build custom ones, you'll see stuff like an entity called "Custom Entity" will have a schema name like "new_customentity".

Also, there are views and filtered views in SQL you can use, especially if you care about security trimming your reports.

Was this reply helpful? Yes No
Wayne Walton 13,730 on at

Like (0)

Report

Also, in the old CRM entity structure, the SQL tables were split between the base columns and the customizations made on an entity. This is no longer the case, but if you look at a CRM 2011 database, you'll see a rather different database structure, and I wouldn't want that to cause confusion.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for the enlightening response!

The read-only field seems to represent a view and not a table though.

So I guess I can figure out which tables are used by the view by looking at its select-statement?

Was this reply helpful? Yes No
Wayne Walton 13,730 on at

Like (0)

Report

Yes, but honestly it's a 1:1 relationship in modern CRM. So looking at the table list should be very straightforward once you know the view name.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

On my SQL server there are three tables (all having ABC in the name), so I guess to be safe I should save all three?

Thanks for all the help!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

You can easily export only certain tables from your MySQL database to Dynamics CRM with the help of Skyvia.
It is a code-free ETL service with intuitive UI and powerful mapping settings.

Was this reply helpful? Yes No