SQL Server 常用内置函数

本文用于收集在运维中经常使用的系统内置（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)

Configuration Functions (Transact-SQL)

System Functions (Transact-SQL)

巴特西