What is dbid number

sys.dm_exec_sql_text (Transact-SQL)

  • 3 minutes to read

Scope of application:SQL Server (all supported versions) Azure SQL database

Returns the text of the SQL batch generated by the specified sql_handle is identified. This table value function replaces the system function fn_get_sql.

syntax

Arguments

sql_handle
A token that uniquely identifies a batch that has been running or is in progress. sql_handle is of the data type varbinary (64).

The sql_handle can be obtained from the following dynamic management objects:

plan_handle
A token that uniquely identifies a query execution plan for a batch that has been executed and whose plan is in the plan cache or in the process of being executed. plan_handle is varbinary (64)

plan_handle can be obtained from the following dynamic management objects:

Returned table

Column nameData typeDESCRIPTION
DBIDsmallintID of the database.

For ad hoc statements and prepared SQL statements, the ID of the database in which the statements were compiled.
objectidintID of the object.

This value is NULL for ad hoc statements and prepared SQL statements.
numbersmallintFor a numbered stored procedure, this column returns the number of the stored procedure. For more information, see sys.numbered_procedures (Transact-SQL).

This value is NULL for ad hoc statements and prepared SQL statements.
.bit1 = The SQL text is encrypted.

0 = The SQL text is not encrypted.
textnvarchar (max)Text of the SQL query.

The value is NULL for encrypted objects.

Permissions

Requires permission on the server.

Remarks

For ad hoc queries, the SQL handles are hashed values ​​based on the SQL text that is sent to the server. They can come from any database.

For database objects, e.g. For example, stored procedures, triggers or functions, the SQL handles are derived from the database ID, object ID and object number.

The plan handle is a hash value derived from the compiled plan of the entire batch.

Note

DBID can not go out sql_handle for ad hoc queries. Around DBID for ad hoc queries, use plan_handle .

Examples

A. Conceptual example

The following is a simple example that shows how to submit a sql_handle either directly or with CROSS APPLY.

  1. Create activity.
    Run the following T-SQL code in a new query window in SQL Server Management Studio.

  2. Using CROSS APPLY.
    The sql_handle of the sys.dm_exec_requests is made using CROSS APPLY at sys.dm_exec_sql_text transmitted. Open a new query window and pass in the SPID you identified in step 1. In this example, the SPID is.

  3. Direct transfer from sql_handle .
    Call the sql_handle of sys.dm_exec_requests from. Then pass the sql_handle directly to sys.dm_exec_sql_text. Open a new query window and pass in the SPID you identified in step 1 sys.dm_exec_requests. In this example, the SPID is. Then pass the returned sql_handle as an argument sys.dm_exec_sql_text.

B. Get information about the five queries with the highest average CPU time

The following example returns the text of the SQL statement and the average CPU time for the five queries with the highest average CPU time.

C. Provide statistics on batch execution

The following example returns the text of SQL queries executed in batches. Statistical information about the queries is also provided.

See also

Dynamic Administrative Views and Features (Transact-SQL)
Dynamic management views and functions related to execution (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL)
sys.dm_exec_requests (Transact-SQL)
sys.dm_exec_cursors (Transact-SQL)
sys.dm_exec_xml_handles (Transact-SQL)
sys.dm_exec_query_memory_grants (Transact-SQL)
Using Apply
sys.dm_exec_text_query_plan (Transact-SQL)