Anatomy of SQL Server 15th where the source text of SQL Server Stored Procedures is stored

Anatomy of SQL Server 15th where is the source text stored in SQL Server Stored Procedures stored? (Translated) improve. dkwhere-does-SQL-server-store-the-source-for-stored-procedures currently I am extending the function of OrcaMDFStudio not only to support system tables, DMVs and user tables must also support stored procedures. That

Anatomy of SQL Server 15th where is the source text stored in SQL Server Stored Procedures stored? Http://improve.dk/where-does-sql-server-store-the-source-for-stored-procedures/ I am currently extending the functionality of OrcaMDF Studio not only to support system tables, DMVs and user tables, but also to support stored procedures. That

Anatomy of SQL Server 15th where is the source text stored in SQL Server Stored Procedures stored? Translation)

Http://improve.dk/where-does-sql-server-store-the-source-for-stored-procedures/

Currently, I am extending the function of OrcaMDF Studio to support not only system tables, but also DMVs and user tables. That's easy. We only need to query sys. procedures -- or query sys. sysschobjs,

Because when SQL server is not running, we cannot query sys. procedures.

However, I don't want to just list the Stored Procedure names. I also need to display the source code in the stored procedure. This brings about a new task-retrieving source code. Where is the source code stored?

I can't find any useful information on Google, so we can only observe it on our own!

I have created a new empty database. This database has a 3 mb data file. In this database, I have created a separate stored procedure like this:

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        -- Create date: -- Description:    -- =============================================CREATE PROCEDURE XYZ    ASBEGIN    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    SET NOCOUNT ON;    -- Insert statements for procedure here    SELECT 'AABBCC' AS OutputEND

Now, when I select * from sys. procedures, we can see that the object ID of the stored procedure is 2105058535

select * from sys.procedures

So far, everything went well. Then we can retrieve the definition of the stored procedure and use the sys. SQL _modules view to query the definition TEXT OF THE nvarchar (MAX) type.

select * from sys.sql_modules where object_id = 2105058535

The above is the source code of the XYZ stored procedure! Wait, I can get the object ID of the stored procedure from the sys. sysschobjs table.
Sys. SQL _modules and sys. SQL _modules are just a view rather than a system table. Let's take a look at how the sys. SQL _modules view gets definitions:

select object_definition(object_id('sys.sql_modules'))

SELECT    object_id = o.id,    definition = Object_definition(o.id),    uses_ansi_nulls = Sysconv(bit, o.status & 0x40000), -- OBJMOD_ANSINULLS    uses_quoted_identifier = sysconv(bit, o.status & 0x80000),   -- OBJMOD_QUOTEDIDENT    is_schema_bound = sysconv(bit, o.status & 0x20000),    -- OBJMOD_SCHEMABOUND    uses_database_collation = sysconv(bit, o.status & 0x100000),  -- OBJMOD_USESDBCOLL    is_recompiled = sysconv(bit, o.status & 0x400000),     -- OBJMOD_NOCACHE    null_on_null_input = sysconv(bit, o.status & 0x200000),   -- OBJMOD_NULLONNULL    execute_as_principal_id = x.indepidFROM    sys.sysschobjs oLEFT JOIN    sys.syssingleobjrefs x ON x.depid = o.id AND x.class = 22 AND x.depsubid = 0 -- SRC_OBJEXECASOWNERWHERE    o.pclass <> 100 AND    (        (o.type = 'TR' AND has_access('TR', o.id, o.pid, o.nsclass) = 1) OR        (type IN ('P','V','FN','IF','TF','RF','IS') AND has_access('CO', o.id) = 1) OR        (type IN ('R','D') AND o.pid = 0)    )

If you use sqlprompt, you can directly display the definition without executing the object_definition function.

The sys. SQL _modules view also uses the system function object_definition to obtain the code.
Unfortunately, the following code cannot work

select object_definition(object_id('object_definition'))

I happen to remember that an obsolete view can replace sys. SQL _modules and sys. syscomments.
Let's take a look at the obtained code.

select object_definition(object_id('sys.syscomments'))

SELECT    o.id AS id,      convert(smallint, case when o.type in ('P', 'RF') then 1 else 0 end) AS number,      s.colid,    s.status,      convert(varbinary(8000), s.text) AS ctext,      convert(smallint, 2 + 4 * (s.status & 1)) AS texttype,      convert(smallint, 0) AS language,      sysconv(bit, s.status & 1) AS encrypted,      sysconv(bit, 0) AS compressed,      s.text  FROM    sys.sysschobjs oCROSS APPLY    OpenRowset(TABLE SQLSRC, o.id, 0) s  WHERE    o.nsclass = 0 AND    o.pclass = 1 AND    o.type IN ('C','D','P','R','V','X','FN','IF','TF','RF','IS','TR') AND    has_access('CO', o.id) = 1  UNION ALL  SELECT    c.object_id AS id,      convert(smallint, c.column_id) AS number,      s.colid,    s.status,      convert(varbinary(8000), s.text) AS ctext,      convert(smallint, 2 + 4 * (s.status & 1)) AS texttype,      convert(smallint, 0) AS language,      sysconv(bit, s.status & 1) AS encrypted,      sysconv(bit, 0) AS compressed,      s.text  FROM    sys.computed_columns cCROSS APPLY    OpenRowset(TABLE SQLSRC, c.object_id, c.column_id) s  UNION ALL  SELECT    p.object_id AS id,      convert(smallint, p.procedure_number) AS number,      s.colid,    s.status,      convert(varbinary(8000), s.text) AS ctext,      convert(smallint, 2 + 4 * (s.status & 1)) AS texttype,      convert(smallint, 0) AS language,      sysconv(bit, s.status & 1) AS encrypted,      sysconv(bit, 0) AS compressed,      s.text  FROM    sys.numbered_procedures pCROSS APPLY    OpenRowset(TABLE SQLSRC, p.object_id, p.procedure_number) s  UNION ALL  SELECT    o.id AS id,      convert(smallint, case when o.type in ('P', 'RF') then 1 else 0 end) AS number,      s.colid,    s.status,      convert(varbinary(8000), s.text) AS ctext,      convert(smallint, 2) AS texttype,      convert(smallint, 0) AS language,      sysconv(bit, 0) AS encrypted,      sysconv(bit, 0) AS compressed,      s.text  FROM    sys.sysobjrdb oCROSS APPLY    OpenRowset(TABLE SQLSRC, o.id, 0) s  WHERE    db_id() = 1 AND     o.type IN ('P','V','X','FN','IF','TF')

He does not use object_definition, but uses another internal function Format: OpenRowset (table sqlsrc, o. id, 0 ). I won't give up easily -- I reverse the OpenRowset (TABLE RSCPROP) Function

Let's use different methods to solve this problem. The storage of anything in SQLSERVER uses a fixed 8 KB page format. When stored procedures are not encrypted, they must be stored somewhere in the database in plain text-but we don't know where they are.

We split the database and used the hex editor for cracking (I recommend using the hex editor HxD)

Download the HxD hex Editor:

Http://files.cnblogs.com/lyhabc/HxDhex%E7%BC%96%E8%BE%91%E5%99%A8.rar

In order to locate the location of the stored procedure, I intentionally used the "SELECT 'aabbcc 'string in the stored procedure.
So that we can easily find the location of the stored procedure:

We found:

Now the code is stored in the database. The data is stored in the data file with the offset of 0x00101AF0. The decimal value is 01055472. We know that the data page is 8 KB. We can calculate the page number of the Code.

01055472/8192 = 128

Now we know that the code is stored on PAGE 128 -- we re-append the database and use dbcc page to check the PAGE content:

-- Only display the data PAGE header dbcc traceon (3604) godbcc page (Test2, 1,128, 0) GO

NOTE: For the dbcc page command, I use the PAGE Style 0 for execution. Here I just want to view the data page header-there will be something interesting

As expected, this is a normal data page. The m_type field shows 1 (type id 1 indicates this is the data page in the database)
More interestingly, we can see that the page belongs to object ID 60! Let's take a look at what object ID 60 is:

select * from sys.sysobjects where id = 60

Let's take a look at sys. sysobjvalues. Note: When you query the sys. sysobjvalues view, you need to use the DAC connection to see that it is actually an internal system table:

select * from sys.sysobjvalues

We do not need to care about a lot of content shown here, but we need to try to filter out the information of our stored procedure object ID 2105058535:

select * from sys.sysobjvalues where objid = 2105058535

I want to know what the imageval column contains. If I remember correctly, 0x2D2D should be "-" in ASCII.
This reminds me that at the beginning of the XYZ stored procedure, we tried to convert the value of this column into our readable form.

select convert(varchar(max), imageval) from sys.sysobjvalues where objid = 2105058535

Dear reader, this is the source code of the XYZ stored procedure, which is stored in the sys. sysobjvalues system table.
In the last example, the source code list of a stored procedure is retrieved without relying on the object_definition () function and the sys. SQL _modules view.

select    p.name,    cast(v.imageval as varchar(MAX))from    sys.procedures pinner join    sys.sysobjvalues v on p.object_id = v.objid

15th