select * from master.dbo.sysprocesses 
where DB_NAME(dbid)=’test’ 
and spid<>@@SPID;

exec sp_lock
exec sp_who2 SPID;

select request_session_id, resource_type, resource_description, request_mode, request_status, request_owner_type
from sys.dm_tran_locks;

kill SPID;

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'yourTableName';

-- 当前时间
select getdate();
-- 当前日期
SELECT CAST( GETDATE() AS Date ) ;
-- 日期增减计算
DATEADD(DAY, -30, GETDATE());
-- 字符串转日期
Convert(DATETIME, '19990101', 112)；
-- datetime2和datetime的区别
-- datetime2支持的日期范围更广，00010101-99991231
-- datetime的年支持范围为1753-9999
-- 如果遇到datetime2往datetime里插入的要特别注意
-- 有小于1753年的数据则会报错  The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range

-- 查看字符串长度
SELECT LEN(FirstName) AS Length, FirstName, LastName   
FROM Sales.vIndividualCustomer  
WHERE CountryRegionName = 'Australia'; 
-- 查看字节长度
SELECT length = DATALENGTH(EnglishProductName), EnglishProductName  
FROM dbo.DimProduct  
ORDER BY EnglishProductName;  
-- 字符串转换为数字类型
select cast('123' as int);
select convert(int, '123');
-- 翻转字符串
SELECT reverse('description')
-- 查找特定字符串位置, 从1开始计数
SELECT charindex('cri', 'description'); -- 4
SELECT charindex('i', 'description', 9); -- 9
-- 区分大小写
SELECT charindex('crI', 'description' COLLATE Latin1_General_CS_AS); -- 0
-- 截取字符串
SELECT substring('description', 5, 3); -- rip
-- 截取最后一个单词
SELECT substring('Robust LDAP server for Java', len('Robust LDAP server for Java') - charindex(' ', reverse('Robust LDAP server for Java'), 1) + 1, charindex(' ', reverse('Robust LDAP server for Java'), 1))

SELECT * INTO ABC_1 FROM ABC
-- 只复制表结构
-- where 1 = 2;

ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2);  

INSERT into table2
   SELECT *
   FROM table1 t1
   WHERE NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t2.id = t1.id);

UPDATE R 
SET R.status = '0' 
FROM dbo.ProductReviews AS R
INNER JOIN dbo.products AS P 
       ON R.pid = P.id 
WHERE R.id = '17190' 
  AND P.shopkeeper = '89137';

-- EXCEPT 相当于oracle里的 minus
SELECT CustId, CustName, CustAddress, CustPhone FROM Original
EXCEPT
SELECT CustId, CustName, CustAddress, CustPhone FROM Revised

-- 时间戳+7位随机数
SELECT DATEDIFF(s, '1970-01-01', GETUTCDATE()) * 1e7  + (ABS(CHECKSUM(NEWID()))% 10000000)