本文用于收集在运维中经常使用的系统内置(built-in)函数,持续整理中
一,常用的获取元数据的函数
1,查看数据库的ID和Name
db_id(‘DB Name’),db_name('DB ID')
2,查看对象的ID和Name,对象的Schema,对象的定义
OBJECT_ID ( 'schema_name . object_name','object_type' )
OBJECT_NAME ( object_id [, database_id ] )
OBJECT_SCHEMA_NAME ( object_id [, database_id ] )
OBJECT_DEFINITION ( object_id )
3,查看Schema的ID和Name,通过对象ID获取对象的架构名(Schema)
SCHEMA_NAME ( [ schema_id ] )
SCHEMA_ID ( [ schema_name ] )
OBJECT_SCHEMA_NAME ( object_id [, database_id ] )
4,查看Column的Name
COL_NAME(table_id,column_id)
二,常用全局变量
1,SQL Server的Name,ServiceName和版本
@@SERVERNAME,@@SERVICENAME,@@VERSION
查看服务器名字,也可以使用函数 serverproperty('servername'),有时 serverproperty('servername') 和 @@servername 返回的值不同,这可能是修改服务器的名称导致,使用如下脚本修复,并重启service,检查服务器的名称:
--将两者协调一致,再重启 SQL Server 服务
if serverproperty('servername')<>@@servername
begin
declare @server sysname
set @server= @@servername
exec sys.sp_dropserver @server = @server
set @server = cast(serverproperty('servername') as sysname)
exec sys.sp_addserver @server = @server ,@local = 'LOCAL'
end use master
go
select name ,@@servername,serverproperty('servername')
from sys.servers
where server_id=0 --Local Server ID = 0
go
2,返回当前module的ID,module包括:SP,UDF,Trigger
@@PROCID --获取当前Module Name
declare @ObjectName sysname;
select @ObjectName=object_name(@@ProcID)
3,返回当前Session的ID,当前的RequestID
@@SPID
CURRENT_REQUEST_ID()
4,在当前Session中,返回上一条Query影响的数据行数量
@@ROWCOUNT
ROWCOUNT_BIG ( )
5,当前Connection中,返回已开启,但未结束的事务数量,查看当前事务的ID,和事务的状态(1,0,-1)
XACT_STATE() 函数返回事务的状态,1表示有Active Transaction,0表示没有Active Transaction,-1表示有Active Transaction,但是有错误发生导致该事务未被提交。
@@TRANCOUNT
CURRENT_TRANSACTION_ID( )
XACT_STATE()
6,查看当前机器(Host)的名字(Machine Name和ID)
HOST_NAME () ,HOST_ID()
三,使用GZIP algorithm压缩数据和解压缩数据
COMPRESS ( expression )
DECOMPRESS ( expression )
在插入数据时,压缩数据,压缩之后的数据类型是varbinary(max)
INSERT INTO player (name, surname, info )
VALUES (N'Ovidiu', N'Cracium', COMPRESS(N'{"sport":"Tennis","age": 28,"rank":1,"points":15258, turn":17}'));
在查询数据时,解压缩数据,将数据从varbinary(max)强转为原始类型
SELECT _id, name, surname, datemodified, CAST(DECOMPRESS(info) AS NVARCHAR(MAX)) AS info
FROM player;
四,调试函数
1,获取异常消息
在TSQL中,使用try 和 catch编写异常处理代码,在catch子句中,使用debug函数,能够获取异常信息
--返回发生错误的代码行号(LineNumber)
ERROR_LINE ( )
--返回错误号(ErrorNumber)
ERROR_NUMBER ( )
@@ERROR
--返回错误消息(ErrorMessage)
ERROR_MESSAGE ( )
--返回发生错误的SP Name
ERROR_PROCEDURE ( )
--返回错误的严重度(Error Severity)
ERROR_SEVERITY ( )
--返回错误的状态(Error State)
ERROR_STATE()
在进行调试时,可以以下示例脚本代码,将异常信息记录在数据表中,以便进行代码的故障排除
-- SET XACT_ABORT ON will render the transaction uncommittable when the constraint violation occurs.
SET XACT_ABORT ON; BEGIN TRY
BEGIN TRANSACTION;
-- A FOREIGN KEY constraint exists on this table. This statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
-- If the delete operation succeeds, commit the transaction. The CATCH block will not execute.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Test XACT_STATE for 0, 1, or -1.
-- If 1, the transaction is committable.
-- If -1, the transaction is uncommittable and should be rolled back.
-- XACT_STATE = 0 means there is no transaction and a commit or rollback operation would generate an error. -- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
--Logging Exception info, as the transaction is in an uncommittable state. Rolling back transaction.
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
--'The transaction is committable. Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
GO
2,抛出异常消息
在SQL Server 2012及之后的版本中,使用 Throw 关键字代替RaiseError,用于抛出异常,并将执行控制权转移到Catch 代码块
THROW [error_number, error_message, error_state];
五,DBCC 命令
1,查看数据库的隔离级别
DBCC USEROPTIONS
参考文档:
Metadata Functions (Transact-SQL)