Technical Articles
How to run SQL Queries Directly on SAP Database
Hi,
If you ever wanted to write SQL queries to read/analyze data from SAP tables, then this post will be very useful for you.
To run SQL queries directly, you need to open tcode “dbacockpit”. Expand Diagnostic menu and run SQL Editor. Write your query and click Execute. That is it.
If you want to save your query for later use, you can give a name and click save.
In that way you do not need to ask help of Abap consultant to change or query data from tables. You can quickly analyze data and if necessary you make changes on data too.
Hope, that quick tip helps.
DBACockpit – Query Editor
Result View
Join Query Example :
select mara.matnr, mkal.verid
from mara
inner join mkal on mara.matnr = mkal.matnr
Nice tip.
Don't forget that your queries should include the selection of the client using a WHERE clause like
or
otherwise the queries are cross-client.
Well, if person has permission to use DBACockpit and is brave enough to run SQL queries directly on database, I would assume, that person would know that 🙂
I would assume he will also be aware of the SQL editor in DBACookpit in such case... 😉
I use SQL console of ADT for these cases:
This is how the join result looks like in ADT.
N.B.: Only ABAP SQL statements are allowed in the ADT SQL console. Native SQL statements are not supported
You can also use this open source report to also run native sql
https://github.com/marianfoo/ztoad
To your comment:
While this may be a practical technical approach, as a best practice I would not recommend executing direct SQL statements in production for performance reasons and certainly not updates to standard SAP tables to ensure data integrity and audit compliance.
A safer alternative would be to run application level end user query/reporting tools, such as the Custom Analytical Queries app in the case of S/4HANA.
It's a nice tip but I suspect very few non-Basis users have access to the DB transactions. As a developer, I had it in one of the previous jobs where my duties included much more than ABAP. But since then I don't recall having it in any projects.
For developers, the SQL console that Suhas mentioned, is most suitable option and works just fine. For functional consultants, good old SQVI or analytical queries mentioned by Mustafa exist and don't require much knowledge of SQL.
As a side note, the text says "you do not need to ask help of Abap consultant" but then this is posted in ABAP tag. Ironic. 🙂
Hi Jelena and other critics :), thanks for comments. Don't think it only for querying tables, think about CRUD operations too. Of course SE16H can be used with debug options but this is much faster, only if you know what you are doing.
Cheers.
I would kindly advise to avoid of any direct CRUD updates via the SQL console.
Not sure CRUD are possible in ECC. I tried all kinds of syntax, and non of them worked. Which is actually good in my company case. We wanted onlt select statement.
I agree completely with Shai. Stay away with C, U, and even more so, D part. That's where you should see a doctor... err... an ABAP developer.
Recently I have used DBA Cockpit to unlock my account on a different client(010).
To check entries on my user
To unlock my user
Sometimes it is just "YES"
And a video tutorial.