System Store
Procedure:
system stored
procedure is any stored procedure with a name that starts with sp_ that
is found in the master database. These SP's can be executed from any
database and will run from the context of that database. Any time you execute an
SP that starts with sp_ SQL Server goes directly to the master database
to find it.
Master Database:
The master database
records all the system-level information for a SQL Server system. This includes
instance-wide metadata (data about data) such as logon accounts, endpoints,
linked servers, and system configuration settings. Also, master is the database
that records the existence of all other databases and the location of those
database files and records the initialization information for SQL Server.
Therefore, SQL Server
cannot start if the master database is unavailable. In SQL Server, system
objects are no longer stored in the master database; instead, they are stored in
the Resource database.
create own System
Store procedure:
USE MASTER
GO
CREATE PROCEDURE
[dbo].[sp_LockDetail]
AS
BEGIN
SELECT
SessionID = s.Session_id,
resource_type,
DatabaseName = DB_NAME(resource_database_id),
request_mode,
request_type,
login_time,
host_name,
program_name,
client_interface_name,
login_name,
nt_domain,
nt_user_name,
s.status,
last_request_start_time,
last_request_end_time,
s.logical_reads,
s.reads,
request_status,
request_owner_type,
objectid,
dbid,
a.number,
a.encrypted
,
a.blocking_session_id,
a.text
FROM
sys.dm_tran_locks
l
JOIN sys.dm_exec_sessions
s ON l.request_session_id
= s.session_id
LEFT JOIN
(
SELECT *
FROM
sys.dm_exec_requests
r
CROSS
APPLY sys.dm_exec_sql_text(sql_handle)
)
a
ON s.session_id
= a.session_id
WHERE
s.session_id >
50
END
Note: That store procedure store in master
Database.
Output:
Additional
Resources: MSDN