SQL Server 常用内置函数（built-in）持续整理

lbdbzj110

　　本文用于收集在运维中经常使用的系统内置函数，持续整理中
　　一，常用Metadata函数
　　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)
　　二，常用Security Function
　　1，当前数据库User的Name（Database User）



CURRENT_USER
　　2，查看数据库User的Name 和ID（Database User）



USER_NAME ( [ id ] )
USER_ID ( [ 'user' ] )
USER_SID()
DATABASE_PRINCIPAL_ID ( 'principal_name' )  
　　3，查看服务器User的Name和ID（Server User，即Login）
　　Login（登录）是服务器主体（Server Principal），有Name和ID属性，每一个Login都有一个安全标识（SID）。



--返回Login ID，参数是Login Name
SUSER_ID ( ['login'] )            
--返回Login Name,参数是Login ID,         
SUSER_NAME ( [login_id] )
--返回SID（security identification），参数是Login Name
SUSER_SID ( ['login'] [ , Param2 ] )   
--返回Login Name，参数是SID
SUSER_SNAME ( [SID] )   
　　4，SID和ID的区别
　　当创建一个SQL Server Login时，从sys.server_principals 系统视图中，能够看到该Login被指定一个ID和SID，在SQL Server实例中，ID和SID都是唯一的，不同之处是，ID标识Login，将Login作为一个Securable ，SID标识Login的Security Context。一般来说，ID可以重用，但是SID一般是不会重用的。当将同一个Login重复创建时，其ID可能发生变化，但是其SID不变。
　　不仅Login有ID和SID，Database User也有。当一个database users创建时，从sys.database_principals中，能够看到该User被指定一个ID和SID。在数据库级别，ID是唯一的。如果User是从SQL Server Login创建的，那么User和Login的SID相同。 详情，请参考《SIDs and IDs》
　　三，常用全局变量
　　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
View Code　　2，抛出异常消息
　　在SQL Server 2012及之后的版本中，使用 Throw 关键字代替RaiseError，用于抛出异常，并将执行控制权转移到Catch 代码块



THROW [error_number, error_message, error_state];
　　六，DBCC 命令
　　1，查看数据库的隔离级别



DBCC USEROPTIONS
　　参考文档：
　　Security Functions (Transact-SQL)
　　Metadata Functions (Transact-SQL)
　　Configuration Functions (Transact-SQL)
　　System Functions (Transact-SQL)