MSSQL²éÕÒ½ø³ÌÔì³ÉËÀËø_°ÑÕâ¸ö½ø³Ìɱµô
create proc [dbo].[sp_lockinfo]
@kill_lock_spid bit=0, --ÊÇ·ñɱµô×èÈûµÄ½ø³Ì,1 ɱµô, 0 ½öÏÔʾ
@show_spid_if_nolock bit=0, --Èç¹ûûÓÐ×èÈûµÄ½ø³Ì,ÊÇ·ñÏÔʾÕý³£½ø³ÌÐÅÏ¢,1 ÏÔʾ,0 ²»ÏÔʾ
@dbname sysname='' --Èç¹ûΪ¿Õ,Ôò²éѯËùÓеĿâ,Èç¹ûΪnull,Ôò²éѯµ±Ç°¿â,·ñÔò²éѯָ¶¨¿â
as
set nocount on
declare @count int,@s nvarchar(2000),@dbid int
if @dbname='' set @dbid=db_id() else set @dbid=db_id(@dbname)
select id=identity(int,1,1),±êÖ¾,
½ø³ÌID=spid,Ïß³ÌID=kpid,¿é½ø³ÌID=blocked,Êý¾Ý¿âID=dbid,
Êý¾Ý¿âÃû=db_name(dbid),Óû§ID=uid,Óû§Ãû=loginame,ÀÛ¼ÆCPUʱ¼ä=cpu,
µÇ½ʱ¼ä=login_time,´ò¿ªÊÂÎñÊý=open_tran, ½ø³Ì״̬=status,
¹¤×÷Õ¾Ãû=hostname,Ó¦ÓóÌÐòÃû=program_name,¹¤×÷Õ¾½ø³ÌID=hostprocess,
ÓòÃû=nt_domain,Íø¿¨µØÖ·=net_address
into #t from(
select ±êÖ¾='×èÈûµÄ½ø³Ì',
spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=a.spid,s2=0
from master..sysprocesses a join (
select blocked from master..sysprocesses
where blocked>0
and(@dbid is
Ïà¹ØÎĵµ£º
sql´æ´¢¹ý³Ì½Ì³Ì
[ËѼ¯ÕûÀí]sql´æ´¢¹ý³ÌÍêÈ«½Ì³Ì
Ŀ¼
1.sql´æ´¢¹ý³Ì¸ÅÊö
2.SQL´æ´¢¹ý³Ì´´½¨
3.sql´æ´¢¹ý³Ì¼°Ó¦ÓÃ
4.¸÷ÖÖ´æ´¢¹ý³ÌʹÓÃÖ¸ÄÏ
5.ASPÖд洢¹ý³Ìµ÷ÓõÄÁ½ÖÖ·½Ê½¼°±È½Ï
6.SQL´æ´¢¹ý³ÌÔÚ.NETÊý¾Ý¿âÖеÄÓ¦ÓÃ
7.ʹÓÃSQL´æ´¢¹ý³ÌÒªÌرð×¢ÒâµÄÎÊÌâ
1.sql´æ´¢¹ý³Ì¸ÅÊö
ÔÚ´óÐÍÊý¾Ý¿âϵͳÖУ¬´æ´ ......
1.
--½«ºº×Öת»»ÎªÆ´ÒôÊ××Öĸ
CREATE function GetAllPY(@str nvarchar(4000))
returns nvarchar(4000)
--WITH ENCRYPTION
as
begin
declare @intLen int
declare @strRet nvarchar(4000)
declare @temp nvarchar(100)
set @intLen &nb ......
--×Ö¶ÎÌí¼Ó˵Ã÷
EXEC sp_addextendedproperty 'MS_Description', 'ÒªÌí¼ÓµÄ˵Ã÷', 'user', dbo, 'table', ±íÃû, 'column', ÁÐÃû
--ɾ³ý×Ö¶Î˵Ã÷
EXEC sp_dropextendedproperty 'MS_Description', 'user', dbo, 'table', ±íÃû, 'column', ×Ö¶ÎÃû
--²é¿´×Ö¶Î˵Ã÷
SELECT
[Table Name] = i_s.TAB ......
Ëø»úÖÆ
NOLOCKºÍREADPASTµÄÇø±ð¡£
1. ¿ªÆôÒ»¸öÊÂÎñÖ´ÐвåÈëÊý¾ÝµÄ²Ù×÷¡£
BEGIN TRAN t
INSERT INTO Customer
SELECT 'a','a'
2. Ö´ÐÐÒ»Ìõ²éѯÓï¾ä¡£
SELECT * from Customer WITH (NOLOCK)
½á¹ûÖÐÏÔʾ"a"ºÍ"a"¡£µ±1ÖÐÊÂÎñ»Ø¹öºó£¬ÄÇôa½«³ÉΪÔàÊý¾Ý¡£(×¢:1ÖеÄÊÂÎñδÌá½») ¡£NOLOCK±íÃ÷ûÓжÔÊý¾Ý±íÌí¼Ó¹²Ï ......