SQL 大全(1)
--查看被锁表:spid 表示锁表进程 ,tableName 表示被锁表名
select request_session_id as spid,
OBJECT_NAME( resource_associated_entity_id) as tableName
from sys .dm_tran_locks where resource_type= 'OBJECT'
--解锁:
declare @spid int
Set @spid = 77 --锁表进程
declare @sql varchar (1000)
set @sql= 'kill '+cast (@spid as varchar)
exec(@sql )
--查看脚本所在地址
select * from sys.all_sql_modules where definition like '%@goodsTable%'
--立即启动作业
use msdb
go
exec sp_start_job @job_name= '退货商品自动添加到网售库存 '
--停止作业
exec sp_stop_job @job_name= '退货商品自动添加到网售库存 '
--添加服务器对象,链接服务器,服务器别名DB01
EXEC sp_addlinkedserver 'DB01', '', 'SQLOLEDB', '10.99.1.4'
EXEC sp_addlinkedsrvlogin 'DB01', 'false',null, 'sa', 'password'
exec sp_serveroption 'WMSDB01', 'rpc out','true'
exec sp_serveroption 'WMSDB01', 'rpc','true'
--查询某一列为数字的集合
SELECT * FROM t_user AS tu WHERE pwd NOT LIKE '%[^0-9]%'
--update or insert
MERGE table1 AS t1
USING (SELECT * FROM table2 ) AS t2
ON ( t1 .groupId = t2 . groupId )
WHEN MATCHED THEN
UPDATE SET t1.ranking = t2. ranking ,t1.UPdatedAt= getdate ()
WHEN NOT MATCHED THEN
INSERT (GroupId,ranking,CreatedAt) VALUES (t2.groupId,t2.ranking,
getdate());