sql server 使用示例

查询表字段等信息

条件 DATA_TYPE（字段类型） 不是必须

SELECT
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    IS_NULLABLE
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_NAME = 'tablename' and DATA_TYPE='nvarchar' ;

查询当前正在执行sql

条件参数：program_name 指是通过.Net 客户端发起
a.program_name =‘.Net SqlClient Data Provider’ 此是.Net Framework 的 SqlClient
a.program_name =‘Core .Net SqlClient Data Provider’ 此是.Net Core的 SqlClient

select 
b.name
,(SELECT TEXT FROM SYS.DM_EXEC_SQL_TEXT(a.SQL_HANDLE) ) FSQLTEXT
,a.*
from sys.sysprocesses a 
inner join sys.databases b on a.dbid = b.database_id
where
b.name ='master' --master 换成正式数据库实体
--and (SELECT TEXT FROM SYS.DM_EXEC_SQL_TEXT(a.SQL_HANDLE) )='xp_cmdshell'
-- hostname ='DTDW7BT2P03V109'
and a.program_name ='Core .Net SqlClient Data Provider'

查询当前正在阻塞的执行语句

SELECT t1.resource_type AS [锁类型], DB_NAME(resource_database_id) AS [数据库名],
t1.resource_associated_entity_id AS [阻塞资源对象],t1.resource_description as [资源描述信息], t1.request_mode AS [请求的锁], 
t1.request_session_id AS [等待会话], t2.wait_duration_ms AS [等待时间],       
(SELECT [text] FROM sys.dm_exec_requests AS r WITH (NOLOCK)                      
	CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) 
    WHERE r.session_id = t1.request_session_id
) AS [等待会话执行的批SQL],
(SELECT SUBSTRING(qt.[text],r.statement_start_offset/2, 
		(CASE WHEN r.statement_end_offset = -1 
		THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 
		ELSE r.statement_end_offset END )/2) 
	FROM sys.dm_exec_requests AS r WITH (NOLOCK)
	CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt
	WHERE r.session_id = t1.request_session_id
) AS [等待会话执行的SQL],                    
t2.blocking_session_id AS [阻塞会话],                                        
(SELECT [text] FROM sys.sysprocesses AS p                                       
	CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle]) 
	WHERE p.spid = t2.blocking_session_id
) AS [阻塞会话执行的批SQL]
FROM sys.dm_tran_locks AS t1 WITH (NOLOCK)
INNER JOIN sys.dm_os_waiting_tasks AS t2 WITH (NOLOCK)
ON t1.lock_owner_address = t2.resource_address OPTION (RECOMPILE);

分页查询

假设有表 tablename，主键是Id , 有创建时间字段 createTime;取1000行后的20行记录。

使用 row_number函数

over函数中 采用Id 排序，也可以使用createTime

select * from tablename t1 Inner join 
 ( select  row_number() over(order by Id) as sortId,Id from tablename ) t2 on t1.Id=t2.Id
  where  t2.sortId>1000 and  t2.sortId<=1020

使用OFFSET和FETCH NEXT

注：此方法是SQL Server 2012之后引入。

select  * from tablename order by Id offset 1000 rows fetch next 20 rows only;