SQLServer笔记

查询XML格式的字段

select top(10) * from Cust_LogBE  where cast(json as nvarchar(max)) like '%20250609002-10%' AND  DocType = 'MES采购订单'  order by CreatedOn desc

锁表处理

-- 查询锁表进程(spid 进程ID)(tableName 被锁表名)
SELECT request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName FROM sys.dm_tran_locks WHERE resource_type='OBJECT';

-- 方式2，查询所有锁表进程服务
SELECT 
    tl.request_session_id AS SessionID,
    db_name(tl.resource_database_id) AS DatabaseName,
    tl.resource_type AS ResourceType,
    tl.resource_description AS ResourceDescription,
    tl.request_mode AS LockType,
    tl.request_status AS RequestStatus,
    OBJECT_NAME(p.object_id) AS ObjectName,
    es.login_name AS LoginName,
    es.host_name AS HostName,
    es.program_name AS ProgramName,
    est.text AS SQLText
FROM 
    sys.dm_tran_locks tl
LEFT JOIN 
    sys.partitions p ON tl.resource_associated_entity_id = p.hobt_id
LEFT JOIN 
    sys.dm_exec_sessions es ON tl.request_session_id = es.session_id
LEFT JOIN 
    sys.dm_exec_requests er ON tl.request_session_id = er.session_id
OUTER APPLY 
    sys.dm_exec_sql_text(er.sql_handle) est
WHERE 
    tl.resource_database_id = db_id()
ORDER BY 
    SessionID, ResourceType;

-- 解锁锁表进程
declare @spid int
SET @spid = 57 -- 锁表进程ID
declare @sql varchar(1000)
SET @sql='kill '+cast(@spid AS varchar)
exec(@sql)

跨服务器查询

-- 建立远程数据库连接
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '172.16.10.46';
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, 'sa ', 'Baiyun2016';
go

INSERT INTO wwwwwww (zbs_codes,zbs_name,zbs_zt) SELECT a.Code,a.ProjectName,c.Description FROM ITSV.baiyun.dbo.Warranties a,ITSV.baiyun.dbo.ProjectTypes b,ITSV.baiyun.dbo.WarrantyCreaters c WHERE a.ProjectTypeId = b.Id AND a.State = c. WarrantyState AND a.Id = c.WarrantyId;
-- 关闭远程连接
exec sp_dropserver 'ITSV', 'droplogins';
go

表关联操作

-- 根据关键字将一个表的数据更新到另外一张表
UPDATE table1 SET table1.string = b.string FROM table2 WHERE table1.ID = table2.ID;

-- 关联更新多表数据
UPDATE Paterson_FrontOrder a INNER JOIN Paterson_FrontOrderline b ON a.ID = b.FrontOrder
SET a.ToDocNo = '',a.ToOrgName = '',a.IsReleased = 0
FROM Paterson_FrontOrder a INNER JOIN  on  WHERE a.ID = 1001906111230907

-- 按表批量修改数据
UPDATE a
SET a.field1 = b.field2
FROM tabl1 a INNER JOIN tabl2 f on fd.ID2 = f.ID
WHERE a.ID = 1

事务操作

-- 查询未关闭的事务进程
select * from master..sysprocesses where open_tran > 0

-- 根据查询结果创建新表
select top 5 a.Name,a.CRptDate,b.Info into table3 from table1 as a inner join table2 as b on a.ID = b.ID2

-- 检查是否锁表
select db_name(dbid),program_name, *from sysprocesses 
where dbid<>11 and ( open_tran>0 or blocked <>0) --or spid =167

-- 杀掉进程
kill 65

远程连接

-- 启用临时远程查询：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

-- 关闭：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

自增ID

SELECT (10 * row_number () OVER (ORDER BY A.DocNo ASC)) AS [RowNo], * FROM #TABLES AS A

根据某一字段分组查询其他字段

SELECT ROW_NUMBER() over (PARTITION BY ParentNode ORDER BY ID DESC) num,ID,Code,Name FROM CBO_NaturalAccount

查询出发器

select * from sys.triggers
exec sp_helptext 'kktr_ShipLine_DescFlexField'

清空表

truncate table Cust_CostControlInvoice

数据库用户角色权限管理

-- 赋予语句跟踪权限
GRANT ALTER TRACE TO CDDBUser1
-- 撤销语句跟踪权限
REVOKE ALTER TRACE FROM CDDBUser1

-- 给完整的EXECUTE执行权限
GRANT EXECUTE TO CDDBUser1
-- 撤销EXECUTE执行权限
REVOKE EXECUTE FROM CDDBUser1

-- 给指定函数执行权限
GRANT EXECUTE ON dbo.F_GetEnumName TO CDDBUser1
-- 撤销函数执行权限
REVOKE EXECUTE ON FUNCTION dbo.F_GetEnumName FROM CDDBUser1

-- 将某个角色赋予指定用户
GRANT my_schema_owner TO CDDBUser1;
-- 撤销指定用户的角色
REVOKE my_schema_owner FROM CDDBUser1;

-- 修改数据库所有者
EXEC sp_configure 'clr enabled', 1 RECONFIGURE WITH OVERRIDE 
 GO 
 Alter Database dbname SET TRUSTWORTHY ON   --将此句中的dbname改为客户实际数据库名
 go 
 use dbname   --将此句中的dbname改为客户实际数据库名
 go 
EXEC dbo.sp_changedbowner @loginame = N'SA', @map = false