²é¿´sql server lock
drop table #t
create table #t(req_spid int,obj_name sysname)
declare @s nvarchar(4000)
,@rid int,@dbname sysname,@id int,@objname sysname
declare tb cursor for
select distinct req_spid,dbname=db_name(rsc_dbid),rsc_objid
from master..syslockinfo where rsc_type in(4,5)
open tb
fetch next from tb into @rid,@dbname,@id
while @@fetch_status=0
begin
set @s='select @objname=name from ['+@dbname+']..sysobjects where id=@id'
exec sp_executesql @s,N'@objname sysname out,@id int',@objname out,@id
insert into #t values(@rid,@objname)
fetch next from tb into @rid,@dbname,@id
end
close tb
deallocate tb
select ½ø³Ìid=a.req_spid
,Êý¾Ý¿â=db_name(rsc_dbid)
,ÀàÐÍ=case rsc_type when 1 then 'NULL ×ÊÔ´£¨Î´Ê¹Óã©'
when 2 then N'Êý¾Ý¿â'
when 3 then N'Îļþ'
when 4 then N'Ë÷Òý'
when 5 then N'±í'
when 6 then N'Ò³'
when
Ïà¹ØÎĵµ£º
±¾ÕÂÊǹØÓÚ²éѯ¡£ÕâÊÇÒ»¸öÖ÷Ì⣬ÔÚºËÐĵĿͻ§/·þÎñÆ÷±à³Ì£¬Òò´ËÕâÊDZ¾Êé¸üÖØÒªµÄƪÕÂÖ®Ò»¡£
¸Ã²ÄÁϽ«±»·Ö³ÉÒÔÏÂÖ÷Òª²¿·Ö£º
ʹÓÃTQuery¶ÔÏó
ʹÓñ¾µØºÍÔ¶³Ì·þÎñÆ÷µÄSQLÀ´Ñ¡Ôñ£¬¸üУ¬É¾³ýºÍ²åÈë¼Ç¼
ʹÓÃSQLÓï¾äÀ´´´½¨Á¬½Ó£¬ÁªÏµÓαêºÍ³ÌÐò£¬ËÑË÷µ¥¸ö¼Ç¼
Õâ¸öËõд´ú±íµÄSQL½á¹¹»¯²éѯÓïÑÔ£¬Í¨³£ÊÇÃ÷ÏÔµÄÐø¼¯»ò˵à ......
//È¡³öÔ´Êý¾Ý
select groupname,totalnum,inputdate,groupid into #temp from
(select count(*)as totalnum,p.groupid,g.groupname,convert(nvarchar(10),inputdate,120) as 'inputdate'
from person p left join admin_group g on p.groupid = g.groupid and deleteflag = '0'
where p.inactive=' ......
ǰÑÔ
SQL Server 2005¿ªÊ¼Ö§³Ö±í·ÖÇø£¬ÕâÖÖ¼¼ÊõÔÊÐíËùÓеıí·ÖÇø¶¼±£´æÔÚͬһ̨·þÎñÆ÷ÉÏ¡£Ã¿Ò»¸ö±í·ÖÇø¶¼ºÍÔÚij¸öÎļþ×é(filegroup)Öеĵ¥¸öÎļþ¹ØÁª¡£Í¬ÑùµÄÒ»¸öÎļþ/Îļþ×é¿ÉÒÔÈÝÄɶà¸ö·ÖÇø±í¡£ÔÚÕâÖÖÉè¼Æ¼Ü¹¹Ï£¬Êý¾Ý¿âÒýÇæÄܹ»Åж¨²éѯ¹ý³ÌÖÐÓ¦¸Ã·ÃÎÊÄĸö·ÖÇø£¬¶ø²»ÓÃɨÃèÕû¸ö±í¡£Èç¹û²éѯÐèÒªµÄÊý¾ÝÐзÖÉ¢ÔÚ¶à¸ö·ÖÇøÖ ......
ÕâÀï½éÉÜsql server2005ÀïÃæµÄÒ»¸öʹÓÃʵÀý£º
CREATE TABLE tb(province nvarchar(10),city nvarchar(10),score int)
INSERT tb SELECT 'ÉÂÎ÷','Î÷°²',3
UNION ALL SELECT 'ÉÂÎ÷','°²¿µ',4
UNION ALL SELECT 'ÉÂÎ÷','ººÖÐ',2
UNION ALL SELECT '¹ã¶«','¹ãÖÝ',5
UNION ALL SELECT '¹ã¶«','Ö麣',2
UNION ......