SQL Server 2000 基础——系统函数 （转帖）

一、系统函数

1、查看信息

1.1、查看编号和名称

select @@SERVERNAME

--SQL SERVER服务器的连接字符串，如：computername/instancename

select @@SERVICENAME

--若为默认实例，则返回MSSQLSERVER；若是命名实例，则返回实例名

改变机器名称后，实例的名称也会跟着变。但是之后会导致本机的内部实例名会跟网络服务名不一致，解决办法如下：

select @@SERVERNAME, SERVERPROPERTY('ServerName')

 

if serverproperty('servername')<>@@servername  

begin  

    declare @server sysname  

    set @server=@@servername  

    exec sp_dropserver @server=@server

--删除这个服务器注册

    set @server=cast(serverproperty('servername') as sysname)  

    exec sp_addserver @server=@server,@local='LOCAL'

--添加当前改过名的服务器注册

end

 

select HOST_ID() --主机编号

select HOST_NAME()--主机名

select DB_ID('master') --数据库编号

select DB_NAME(1)      --数据库名称

select OBJECT_ID('sysobjects')  --数据库对象编号

在临时表上用object_id()时，要在临时表前加数据库名

如下：

方法1(错误)

--#temp不是存在当前数据库中的

select object_ID('#temp')

方法2(错误)

--是存在tempdb中，但temdb中没有这个函数

use tempdb

select object_ID('#temp')

方法3(正确)

--MSQL的临时表是存在tempdb中的

select object_ID('tempdb..#temp')

select OBJECT_NAME(1)           --数据库对象名称

select File_ID('master')        --返回该逻辑文件名的编号

select File_NAME(1)              --返回该文件编号的逻辑文件名

select FILEGROUP_ID('primary')     --返回文件组编号

select FILEGROUP_NAME(1)           --返回文件组名称

 

select COL_NAME(OBJECT_ID('sysobjects'),1)

--表中列的名称，每个表中列是从…n顺序编号

select INDEX_COL('sysobjects',1,1)   

--返回sysobjects中第一个索引第一个键的名称

 

select APP_NAME() --返回当前使用的工具名称，如：SQL 查询分析器

select @@SPID      --返回当前连接在SQL SERVER中的编号

select @@PROCID    --在SP中使用，查看自己的ID

 

1.2、查看登录和用户

select Suser_Sid()      --返回登录的SID，可传参，默认返回当前值

select Suser_Sname()    --返回登录的名称，可传参，默认返回当前值

select System_User      --等价于不带参数的SUER_SNAME()

 

select User_Id()          --返回数据库用户ID，可传参，默认返回当前值

select User_Name()       --返回数据库用户名，可传参，默认返回当前值

select User                --等价于不带参数的USER_NAME()

select Current_User      --等价于不带参数的USER_NAME()

select Session_User      --等价于不带参数的USER_NAME()

 

1.3、查看属性

select Serverproperty('Collation')               --查看服务器的属性

select Databasepropertyex('master','Collation')  --查看数据库的属性

select Sessionproperty('ANSI_NULLS')              --查看当前会话的SET选项  

select Objectproperty(1,'IsTable')               --查看当前对象的信息

select Filegroupproperty('Primary','IsReadOnly') --查看文件组的属性

select Fileproperty('master','SpaceUsed')        --查看文件的属性

select Columnproperty(1,'name','AllowsNull')      --查看列属性

select Indexproperty(1,'ncsysobjects','IsClustered') --查看索引属性

select Indexkey_Property(1,2,1,'ColumnId')           --查看索引键信息

 

select Collationproperty('Chinese_PRC_CI_AS','LCID')

--查看排序规则的信息

select Sql_Variant_Property('abc12345','BaseType')  

--返回sql_variant类型值的信息

 

2、NULL相关的

select COALESCE(null,'first',null,'second',null)

--返回众多表达式中第一个非NULL 表达式的值

select NULLIF('123','123')

--在expression1 与expression2 相等时返回NULL 值若不相等时则返回expression1 的值

select ISNULL(null,'not null value')

--使用指定的替换值替换NULL

select GetANSINull('master')

--查看SET ANSI_NULL_DFLT_ON 的设置

 

3、字符相关

3.1、获取长度

select DATALENGTH('s我s') --返回表达式占用的字节数

select COL_LENGTH('sysobjects','name') --返回列的定义长度(byte)

 

3.2、返回有效的 SQL Server 分隔标识符

SELECT QUOTENAME('abc[]def')

--结果集：[abc[]]def]，]]类似于''两个表示一个

--该函数对于SQL注入的防护非常有效，各种前台开发工具也有类似函数

 

3.3、加密syscomments中的字符内容

select encrypt('原文') --未公开的函数,syscomments中的ctext即用此加密

 

3.4、加密sysxlogins中的登陆密码

select pwdencrypt('原文') --未公开的函数，sysxlogins中的password即用此加密

select pwdcompare('原文','密文') = 1--相同；否则不相同

 

3.5、校验和函数

if (select checksum_agg(binary_checksum(*)) from A)

=(select checksum_agg(binary_checksum(*)) from B)

    print '相等'

else

    print '不相等'

--此处binary_checksum()也可以换成checksum()，只是算法不同而已，效果一样

4、日期时间

4.1、日期时间转换为字符串

select convert(varchar(20),getdate(),23)

--转换为纯日期，格式如：2007-12-18，BOL无记载

select convert(varchar(20),getdate(),24)

--转换为纯时间，格式如：22:19:20，BOL无记载

 

4.2、计算日期时间

通常， 我们把日期进行分割，然后利用分割出来的年、月、日等计算或组合出自己所需要的日期，下面的例子巧妙利用0即1900-01-01 00:00:00.000作为基准进行日期计算。

其中设置@@DATEFIRST为7，即星期天是一周的第一天。

 

--本年的第一天，当前日期与0相差的年数再加给0，默认为1号

Select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

--本季的第一天，当前日期与0相差的季数再加给0，默认为1号

Select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

--本月的第一天，当前日期与0相差的月数再加给0，默认为1号

Select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

--本周的星期一，当前日期与0相差的周数再加给0，默认为1，星期到星期天的值为1-7

--datepart(dw,getdate())中的返回值表示当前日为一星期中的第几天

Select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

--今天的第一秒，当前日期与0相差的天数再加给0，默认时间为0点

SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

 

--去年的最后一天，本年第一天减掉3ms，在Sql Server中datetime是精确到3ms

Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

--上个月的最后一天，本月第一天减掉3ms

Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

--本年的最后一天，明年第一天减掉3ms

Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))

--本月的最后一天，下个月第一天减掉3ms

Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))

--本月的第一个星期一，以本月第6天与0作差

select DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate())), 0)

4.3、当前日期

CURRENT_TIMESTAMP等同于getdate()，返回类型都为datetime

--与getdate()一样，在函数中也是无法使用

 

5、标识属性相关

先讲一个概念问题，identity属性与identity函数的区别，前者是列的属性，后者是一个只可用于select into语句的函数，用于生成序号，使用如下：

select identity(int, 1, 1) as Ordinal into newtable from oldtable

 

SQL Server 中，可以使用SCOPE_IDENTITY()、@@IDENTITY 、IDENT_CURRENT() 来取得最后插入记录的值值，它们的区别在于：

 

 SCOPE_IDENTITY()和@@IDENTITY 都将返回在当前会话的所有表中生成的最后一个标识值，但是，SCOPE_IDENTITY 只在当前作用域内返回值，而@@IDENTITY 不限于特定的作用域。一个作用域就是一个模块——存储过程、触发器、函数或批处理。

也就是说：SCOPE_IDENTITY()只返回当前所运行的insert返回的标识值，对于由当前insert所触发的insert产生的标识值不作返回，在一些强并发的OLTP系统或存在触发器的系统中，往往因为数据库开发人员使用了@@IDENTITY来捕获标识值，会得到比预期标识值大或小的值；

 

IDENT_CURRENT() 返回的是任何会话和任何作用域中的指定表最后生成的标识值。

-- 创建测试表

CREATE TABLE t1(id int IDENTITY,col int)

INSERT t1

    SELECT 1

    UNION ALL

    SELECT 2

CREATE TABLE t2(id int IDENTITY,col int)

 

CREATE TRIGGER TR_insert_t2 ON t2

FOR INSERT

AS

INSERT t1 SELECT 3

 

-- 测试一，当前连接

INSERT t2 VALUES(1)

SELECT [SCOPE_IDENTITY()]=SCOPE_IDENTITY(),

    [@@IDENTITY]=@@IDENTITY,

    [IDENT_CURRENT() For t1]=IDENT_CURRENT(N't1'),

    [IDENT_CURRENT() For t2]=IDENT_CURRENT(N't2')

-- 测试二，当前连接

INSERT t1 VALUES(10)

SELECT [SCOPE_IDENTITY()]=SCOPE_IDENTITY(),

    [@@IDENTITY]=@@IDENTITY,

    [IDENT_CURRENT() For t1]=IDENT_CURRENT(N't1'),

    [IDENT_CURRENT() For t2]=IDENT_CURRENT(N't2')

-- 测试三，开启一个新连接

SELECT [SCOPE_IDENTITY()]=SCOPE_IDENTITY(),

    [@@IDENTITY]=@@IDENTITY,

    [IDENT_CURRENT() For t1]=IDENT_CURRENT(N't1'),

    [IDENT_CURRENT() For t2]=IDENT_CURRENT(N't2')

-- 删除测试表

DROP TABLE t1,t2

 

可以看出：

IDENT_CURRENT()  始终返回指定表最后的标识值

@@IDENTITY       始终返回当前会话最后的标识值，无论是否在同一个作用域，在测试1、2中，可以看到它返回的是触发器中插入记录的标识值，而在测试3中，因为当前会话无插入记录，所以返回NULL

SCOPE_IDENTITY() 始终返回当前会话同一作用域的标识值，所以在测试1、2中，它返回的值不受触发器的影响，而在测试3中，因为当前会话无插入记录，所以返回NULL

二、系统自定义函数

系统自定义函数都保存在master数据库中以fn开头，调用时from后面加二个冒号(::)，不需要指定所有者，如下：

SELECT * FROM ::fn_helpcollations() --系统所支持的所有的语言

select * from ::fn_trace_gettable('c:/trace_file.trc', default)

--跟踪文件可以通过Profiler工具或运行系统的存储过程sp_trace_create建立

事实上很多系统自定义函数只是简单的调用一个扩展存储过程。

本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/seusoftware/archive/2009/01/15/3792129.aspx

 

好好好强大。。。 - 。-