Though not recommended by ESRI sometimes (see Deleting SDE left over tables) it is needed to actually know what is happening below the covers.
For that in SQL Server we have SQL Profiler, just open the profiler and enter the system administrator user of your choice:
I just keep the default options:
And hit Run.
What you must remember is that SDE works with Stored Procedures (from now on SP) but in two ways. The first is a very unique way - they actually call several SPs that run the SQL. The second is using the geometry SPs in the more traditional way.
The way they do it is identical to the previous version (9.3.1). All you have to do is copy paste the parameters that are after the SQL into the query and run it, for example:
Sp_execute('Select * from where Text=@P1','hello world')
ð Select * from where Text='hello world'
Real example:
declare @p1 int
set @p1=29
exec sp_prepexec @p1 output,N'@P1 nvarchar(30),@P2 nvarchar(30),@P3 smallint,@P4 datetime2(7),@P5 datetime2(7),@P6 nvarchar(30),@P7 numeric(38,8),@P8 int,@P9 int',N'INSERT INTO FluentMigrator.GIS.UNIT_TEST (GtmId, Name, Type, Created, Modified, UserId, MaxCurrent, OBJECTID, Shape) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9)',N'666.666.666.1',N'Bob',666,'2011-01-03 11:30:48','2011-01-03 11:30:48',N'royd',NULL,1,1
select @p1
ð First identify the SQL query:
INSERT INTO FluentMigrator.GIS.UNIT_TEST (GtmId, Name, Type, Created, Modified, UserId, MaxCurrent, OBJECTID, Shape) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9)
ð Now replace the parameters with the values in the end of the query:
INSERT INTO FluentMigrator.GIS.UNIT_TEST (GtmId, Name, Type, Created, Modified, UserId, MaxCurrent, OBJECTID, Shape) VALUES (N'123.123.123.1',N'Bob',123,'2011-01-03 11:30:48','2011-01-03 11:30:48',N'royd',NULL,1,1)
And you have the translation.
Sometimes it's a bit less difficult because they don't use parameters, like in:
declare @p1 int
set @p1=17
exec sp_prepare @p1 output,NULL,N'select SDE.SDE.GDB_ITEMS.* from sde.sde.GDB_Items where (Type IN (''{70737809-852C-4A03-9E22-2CECEA5B9BFA}'')',1
select @p1
Here you should simply do a ctrl+h (find and replace) on '' to ' and you will get:
select SDE.SDE.GDB_ITEMS.* from sde.sde.GDB_Items where (Type IN ('{70737809-852C-4A03-9E22-2CECEA5B9BFA}')
(This query return all the feature layers in the SDE)
Of course don't run any insert/update/delete queries because they could mess up with your DB.
The next query updates the something in SDE geometry (probably the layer's envelop) – using the second way SDE is using SPs:
exec sde.sde.SDE_layer_def_envelope_update 5.6843418860808015e-014,5.6843418860808015e-014,5.6843418860808015e-014,5.6843418860808015e-014,0,0,0,0,1679
Be warned that if you don't use any filtering (like me) you will get lots of unneeded lines. If you are a beginner you can ignore lines like (replace SOMETHING/### with your text):
SET SOMETHING ON
exec sp_unprepare ###
exec sp_execute ###
I usually really look at lines containing red text:
They are usually the ones containing the queries.
Why should you use it?
I only use it when my code has a SDE Exception that I don't understand. In 9.3.1 I once had an Exception about a layer not found in the DB but the layer was visible in ArcCatalog. So what I did was run all the queries the SDE did (it has many queries with the layer name) and in the end found out that the problem was the schema of the layer and the user running the query.
So my advice is to use it only when you have no other choice.
