Skip to Content
Technical Articles
Author's profile photo Beyhan MEYRALI

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%20View

Result View

 

Join Query Example :

select mara.matnr, mkal.verid
from mara
inner join mkal on mara.matnr = mkal.matnr

Assigned Tags

      12 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Guido Schöpp
      Guido Schöpp

      Nice tip.

      Don't forget that your queries should include the selection of the client using a WHERE clause like

      mandt='100'

      or

      clnt='100'

      otherwise the queries are cross-client.

      Author's profile photo Beyhan MEYRALI
      Beyhan MEYRALI
      Blog Post Author

      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 🙂

      Author's profile photo Shai Sinai
      Shai Sinai

      I would assume he will also be aware of the SQL editor in DBACookpit in such case... 😉

      Author's profile photo Suhas Saha
      Suhas Saha

      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

       

      Author's profile photo Marian Zeis
      Marian Zeis

      You can also use this open source report to also run native sql

      https://github.com/marianfoo/ztoad

       

      Author's profile photo Mustafa Bensan
      Mustafa Bensan

      To your comment:

      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.

      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.

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      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. 🙂

      Author's profile photo Beyhan MEYRALI
      Beyhan MEYRALI
      Blog Post Author

      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.

      Author's profile photo Shai Sinai
      Shai Sinai

      I would kindly advise to avoid of any direct CRUD updates via the SQL console.

      Author's profile photo Nemanja Simovic
      Nemanja Simovic

      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.

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      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.

      Author's profile photo Beyhan MEYRALI
      Beyhan MEYRALI
      Blog Post Author

      Recently I have used DBA Cockpit to unlock my account on a different client(010).

      To check entries on my user

      select * from USR02 where bname = 'XXXXX'

      To unlock my user

      update USR02 set uflag = '0' where mandt = '010' and bname = 'XXXXX'
      
      commit

      Sometimes it is just "YES" 🙂

       

      And a video tutorial.