SQL Server包含了一些用于检测SQL Server实例的系统设置的系统配置函数口有一些函数带有@@前缀,在之前版本的SQL Server中叫做变量。其他系统函数没有@@前缀,并且接受用于帮助收集有关SQL Server实例或者数据库信息的参数。
1.确定每周的第一天
@@DATEFIRST函数返回SQL Servcr实例指定的每周第一天的值。因为这个值定义了诸如DATEPART和DATEADD等其他日期函数使用的每周工作日datepart的计算,所以这是需要关注的。
示例:SQL Server实例当前每周第一天:
SELECT @@DATEFIRST 'First Day of the Week'
2.查看当前会话使用的语言
@@LANGID系统函数返回一个表示当前用户会话的本地语言标识符的smallint数据类型值,@@LANGUAGE系统函数返回语言名。
示例:查询会话中目前使用的本地语言设置。
SELECT @@LANGID LanguageID,@@LANGUAGE Language
3.查看和设置当前连接锁超时设置
SET LOCK_TIMEOUT命令配置当前会话中语句等待其他连接释放锁的毫秒数。@@LOCK TIMEOUT函数用于以毫秒为单位显示当前连接锁超时设置.
示例:设置和查看当前会话的锁超时值。
--1000毫秒,1秒
SET LOCK_TIMEOUT 1000
SELECT @@LOCK_TIMEOUT
--没有限制
SET LOCK_TIMEOUT -1
4.显示当前存储过程上下文的嵌套级别
@@NESTLEVEL返回存储过程上下文当前的嵌套级别。存储过程嵌套级别代表某个存储过程调用另外一个存储过程的次数。 SQL Server允许存储过程组成最多32次嵌套(非完整)调用。
示例:捕获存储过程上下文的当前嵌套级别。
--第一个过程
CREATE PROCEDURE dbo.usp_QuickAndDirty
AS
SELECT @@NESTLEVEL
GO
--第二个过程
CREATE PROCEDURE dbo.usp_Call_QuickAndDirty
AS
SELECT @@NESTLEVEL
EXEC dbo.usp_QuickAndDirty
GO
创建两个存储过程之后,在调用usp_Call_QuickAndDirty存储过程之前使用@@NESTLEVEL函数:
--返回0级嵌套级别
SELECT @@NESTLEVEL
--返回1和2级嵌套级别
EXEC dbo.usp_Call_QuickAndDirty
5.返回当前SQL Server实例名和SQL Server版本
@@SERVERNAME显示本地服务器名,@@VERSION返回SQL Server实例的版本、日期和处理器信息。
示例:返回当前SQL Server实例名和版本信息:
SELECT @@SERVERNAME SeverName,@@VERSION VersionInformation
6.返回当前连接的会话ID (SPID)
@@SPID返回当前连接的会话ID,可以使用它来识别sp_who系统存储过程或动态管理视图sys.dm_exec_sessions中的额外信息。
示例:返回当前SQL连接的服务器进程标识符。
SELECT @@SPID SPID
7.返回打开事务的数量
@@TRANCOUNT系统函数显示当前连接的活动事务。可以使用这个甬数来检测当前会话中打开事务的数量,并且根据这个信息来相应地COMMIT或ROLLBACK事务。
示例:返回当前连接中活动事务的数量:
BEGIN TRAN t1
SELECT @@TRANCOUNT --返回1
BEGIN TRAN t2
SELECT @@TRANCOUNT --返回2
BEGIN TRAN t3
SELECT @@TRANCOUNT --返回3
COMMIT TRAN
SELECT @@TRANCOUNT --返回2
ROLLBACK TRAN
SELECT @@TRANCOUNT --ROLLBACK之后总是返回0
8.获取之前语句影响的行数
@@ROWCOUNT返回当前范围中上一个T-SQL语句影响的行数的整数值。@@ROWCOUNT_BIG返回bitint值。
示例:返回之前T-SQL语句影响的行:
USE AdventureWorks
GO
SELECT TOP 3 ScrapReasonID
FROM Production.ScrapReason
SELECT @@ROWCOUNT Int_RowCount,ROWCOUNT_BIG() BigInt_RowCount
9.获取系统统计信息
SQL Server有几个内建的系统统计函数。
函 数 | 描 述 |
@@CONNECTIONS | 返回自SQL Server实例上次启动后连接的数量 |
@@CPU_BUSY | 显示自SQL Servcr实例上次启动后CPU繁忙的毫秒数 |
@@IDLE | 显示自SQL Server实例上次启动后总的空闲时问的毫秒数 |
@@IO_BUSY | 显示自SQL Server实例上次启动后花费在I/O操作上的毫秒数 |
@@PACKET_ERRORS | 显示自SQL Server实例上次启动后总的网络包错误数 |
@@PACK_RECEIVED | 显示自SQL Server实例上次启动后从网络上读取的总的输入包数。你可以监视这个数值是在增加还是保持不变,从而猜测是否有网络问题 |
@@PACK_SENT | 显示自SQL Server实例上次启动后发送到网络上的总的输出包数 |
@@TIMETICKS | 显示每一个tick的毫秒数。tick是由特定的毫秒数指定的度量单位(对于Windows系统是31.25毫秒) |
@@TOTAL_ERRORS | 显示自SQL Server实例上次启动后遇到的读,写错误 |
@@TOTAL_READ | 显示自SQL Server实例上次启动后非缓存的磁盘读取次数 |
@@TOTAL_WRITE | 显示自SQL Servet实例上次启动后的磁盘写入数量 |
查询中使用系统统计函数
SELECT 'Connection' FunctionNM,@@CONNECTIONS Value
UNION
SELECT 'CPUBusy',@@CPU_BUSY
UNION
SELECT 'IDLE',@@IDLE
UNION
SELECT 'IOBusy',@@IO_BUSY
UNION
SELECT 'PacketErrors',@@PACKET_ERRORS
UNION
SELECT 'PackRecieved',@@PACK_RECEIVED
UNION
SELECT 'Packsent',@@PACK_SENT
UNION
SELECT 'TimeTicks',@@TIMETICKS
UNION
SELECT 'TotalErrors',@@TOTAL_ERRORS
UNION
SELECT 'TotalRead',@@TOTAL_READ
UNION
SELECT 'TotalWrite',@@TOTAL_WRITE
10.显示数据库和SQL Server设置
使用DATABASEPROPERTYEX系统函数可以获取有关数据库选项的信息。DATABASEPROPERTYEX使用下面的语法:
DATABASEPROPERTYEX ( database , property)
第一个参数是希望探测的数据库名。第二个参数是希望查找的数据库属性。
示例:获取Adventurehlorks数据库的排序规则、状态和恢复模式:
USE AdventureWorks
GO
SELECT DATABASEPROPERTYEX('AdventureWorks','Collation'),
DATABASEPROPERTYEX('AdventureWorks','Recovery'),
DATABASEPROPERTYEX('AdventureWorks','Status')
使用SERVERPROPERTY系统函数可以获取有关SQL Server实例的信息。在它的语法中不需要指定数据库,所以只有一个属性名参数:
SERVERPROPERTY (propertyname)
示例:返回实例的版本和默认排序规则:
SELECT SERVERPROPERTY('Collation'),SERVERPROPERTY('Edition')
11.返回当前数据库ID和名称
DB_ID函数返回数据库整数ID,DB_NAME返回当前数据库的名字(除非指定了参数)。
示例:获取当前数据库系统ID和名称:
SELECT DB_ID() databaseID,DB_NAME() DatabaseNM
内部数据库ID(数据库创建的时候由SQL Server分配)和数据库名一起返回。函数会根据当前数据库上下文返回信息。
它们也都可以接受参数,例如:
SELECT DB_ID('master') databaseID,DB_NAME(1) DatabaseNM
12.返回数据库对象名和ID
OBJECT_ID返回数据库内部指定的数据库对象标识符。OBJECT NAHE根据对象标识符返回对象名。
示例:返回数据库对象的名称和ID:
USE AdventureWorks
GO
SELECT OBJECT_ID('AdventureWorks.HumanResources.Department'),
OBJECT_Name(805577908,DB_ID('AdventureWorks'))
OBJECT.NAME和OBJECT_ID通常与引用数据库对象标识符的系统目录视图或系统函数一起使用。OBJECT_ID函数用于找出某个对象的内部数据库标识符(需要注意的是,对象ID只在特定数据库中是唯一的)。它的第一个参数是对象的名称。第二个可选参数是对象类型,例如,U表示用户定义表,V表示视图,PK表示主键,还有其他一些值可以在sys.objects目录视图的type列中引用。
OBJECT_NAME用于返回某个对象标识符的对象名。第一个参数是对象ID。第二个可进参数是数据库ID——这在跨数据库的不同对象为同一ID时是有用的。
13.返回当前用户会话的应用程序和主机
APP_NAME返回当前SQL Server连接的应用程序名。HOST_ID返回当前连接的工作站标识号,HOST_NAME返回当前连接的工作站名。
示例:显示当前连接到SQL Server实例的应用程序名和主机:
SELECT APP_NAME() as 'Application',
HOST_ID() as 'HOST ID',
HOST_NAME() as 'Host Name'
14.获取当前用户和登录名上下文
SYSTEM_USER函数返回Windows或SQL登录名.USER函数返回当前用户的数据库用户名。
示例1:返回当前用户以及登录名上下文:
SELECT SYSTEM_USER,--登录名
USER --数据库名
示例2:显示表的DEFAULT值
CREATE TABLE #TempExample
(
ExampleColumn varchar(10) NOT NULL,
ModifiedByLogin varchar(55) NOT NULL DEFAULT SYSTEM_USER,
ModifiedByUser varchar(55) NOT NULL DEFAULT USER
)
GO
INSERT #TempExample
(ExampleColumn)
VALUES ('Value A')
SELECT ExampleColumn,ModifiedByLogin,ModifiedByUser
FROM #TempExample
15.查看用户连接选项
可以使用SESSIONPROPERTY函数查看当前用户连接的SET属性。
SELECT SESSIONPROPERTY('ANSI_NULLS') ANSI_NULLS,
SESSIONPROPERTY('ANSI_PADDING') ANSI_PADDING,
SESSIONPROPERTY('ANSI_WARNINGS') ANSI_WARNINGS,
SESSIONPROPERTY('ARITHABORT') ARITHABORT,
SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL') CONCAT_NULL_YIELDS_NULL,
SESSIONPROPERTY('NUMERIC_ROUNDABORT') NUMERIC_ROUNDABORT,
SESSIONPROPERTY('QUOTED_IDENTIFIER') QUOTED_IDENTIFIER