SQL Server 系统表,系统过程,系统变量,函数

sp_detach_db sp_setnetname sp_dropdevice sp_settriggerorder sp_dropextendedproc   sp_spaceused sp_dropextendedproperty sp_tableoption sp_dropmessage sp_unbindefault sp_droptype sp_unbindrule sp_executesql sp_updateextendedproperty sp_getapplock sp_updatestats sp_getbindtoken sp_validname sp_help   sp_who Web 助手过程   sp_dropwebtask sp_makewebtask sp_enumcodepages sp_runwebtask XML 过程   sp_xml_preparedocument sp_xml_removedocument 常规扩展过程   xp_cmdshell xp_logininfo xp_enumgroups xp_msver xp_findnextmsg xp_revokelogin xp_grantlogin xp_sprintf xp_logevent xp_sqlmaint xp_loginconfig xp_sscanf API 系统存储过程 用户在 ADO、OLE DB、ODBC 和 DB-Library 应用程序上运行 SQL Server 事件探查器时，可能会注意到系统存储过程的使用不涉及 Transact-SQL 引用。这些存储过程由用于 SQL Server 的 Microsoft OLE DB 提供程序、SQL Server ODBC 驱动程序和 DB-Library 动态链接库 (DLL) 用来执行数据库 API 功能。这些过程只不过是提供程序或驱动程序所使用的机制，用来传达用户对 SQL Server 的请求。它们仅供用于 SQL Server 的 OLE DB 提供程序、SQL Server ODBC 驱动程序和 DB-Library DLL 在内部使用。不支持从 SQL Server 应用程序显式调用它们。 这些存储过程通过所支持的 API 函数，使得它们的全部功能均可由 SQL Sever 应用程序使用。例如，sp_cursor 系统存储过程的游标功能通过 OLE DB API 游标属性和方法可由 OLE DB 应用程序使用，通过 ODBE 游标特性和函数可由 ODBE 应用程序使用，通过 DB-library 游标库可由 DB-Library 应用程序使用。 这些系统存储过程支持 ADO、OLE DB、ODBC 和 DB-Library 游标库的游标功能： sp_cursor sp_cursorclose sp_cursorexecute sp_cursorfetch sp_cursoropen sp_cursoroption sp_cursorprepare sp_cursorunprepare     这些系统存储过程支持 ADO、OLE DB 和 ODBC 中用于执行 Transact-SQL 语句的比较/执行模型： sp_execute sp_prepare sp_unprepare sp_createorphan 和 sp_droporphans 存储过程用于 ODBC ntext、text 和 image 的处理。 sp_reset_connection 存储过程由 SQL Server 用来支持事务中的远程存储过程调用。 sp_sdidebug 存储过程由 SQL Server 用来调试 Transact-SQL 语句。    select APP_NAME ( ) --当前会话的应用程序 select @@ERROR     --返回最后执行的 Transact-SQL 语句的错误代码(integer) select @@IDENTITY    --返回最后插入的标识值 Select USER_NAME()     --返回用户数据库用户名 select @@ERROR    --返回最后执行的 Transact-SQL 语句的错误代码 Select @@CONNECTIONS   --返回自上次SQL启动以来连接或试图连接的次数。 Select GETDATE() --当前时间 Select @@CPU_BUSY/100   --返回自上次启动SQL 以来 CPU 的工作时间，单位为毫秒 USE tempdb Select @@DBTS   --为当前数据库返回当前 timestamp 数据类型的值。这一 timestamp 值保证在数据库中是唯一的。 select @@IDENTITY --返回最后插入的标识值 Select @@IDLE   --返回SQL自上次启动后闲置的时间，单位为毫秒 Select @@IO_BUSY    --返回SQL自上次启动后用于执行输入和输出操作的时间，单位为毫秒 Select @@LANGID    --返回当前所使用语言的本地语言标识符(ID)。 Select @@LANGUAGE    --返回当前使用的语言名 Select @@LOCK_TIMEOUT   --当前会话的当前锁超时设置，单位为毫秒。 Select @@MAX_CONNECTIONS   --返回SQL上允许的同时用户连接的最大数。返回的数不必为当前配置的数值 EXEC sp_configure   --显示当前服务器的全局配置设置 Select @@MAX_PRECISION --返回 decimal 和 numeric 数据类型所用的精度级别，即该服务器中当前设置的精度。默认最大精度38。 select @@OPTIONS   --返回当前 SET 选项的信息。 Select @@PACK_RECEIVED   --返回SQL自启动后从网络上读取的输入数据包数目。 Select @@PACK_SENT   --返回SQ自上次启动后写到网络上的输出数据包数目。 Select @@PACKET_ERRORS   --返回自SQL启动后，在SQL连接上发生的网络数据包错误数。 Select @@SERVERNAME --返回运行SQL服务器名称。 Select @@SERVICENAME --返回SQL正在其下运行的注册表键名 Select @@TIMETICKS --返回SQL服务器一刻度的微秒数 Select @@TOTAL_ERRORS   --返回 SQL服务器自启动后，所遇到的磁盘读/写错误数。 Select @@TOTAL_READ   --返回 SQL服务器自启动后读取磁盘的次数。 Select @@TOTAL_WRITE   --返回SQL服务器自启动后写入磁盘的次数。 Select @@TRANCOUNT   --返回当前连接的活动事务数。 Select @@VERSION   --返回SQL服务器安装的日期、版本和处理器类型。 常用的系统存储过程 •常用的sp §sp_who ≋查看连接用户、客户端机器、连接用的程序 ≋小心断开的连接可能继续被sp_who查出，连接池有延时 §sp_executesql ≋执行一个T-SQL语句 ≋用以绕过某些语句要求参数为常数的检查 ‣http://gcdn.grapecity.com/cs/forums/thread/63.aspx ‣http://xa-sps/sites/appkb/Lists/Technical%20Tips%20and%20Trciks/DispForm.aspx?ID=22 §sp_helptext ≋     查看存储过程、视图等对象的生成脚本 •注意版本差异 §在2000中用来创建账号的系统存储过程sp_adduser, sp_addlogin, sp_dropuser, sp_droplogin等已经不推荐使用，取而代之的是Create User, Create Login, Drop User, Drop Login等命令。 常用的扩展存储过程 xp_cmdshell SQL Server 2005中xp_cmdshell默认是禁用的，通过SQL Server Surface Area Configuration进行设定（~ for features） xp_availablemedia xp_dirtree ’C:/’,1,1 常用的通用函数 元数据函数（Metadata Functions） 数学函数（Mathematical Functions） 汇总计算函数（Aggregate Functions） 字符串函数（String Functions） 日期时间函数（Date&Time Functions） 数据类型转换函数 元数据函数 DB_ID(‘xxx’) DB_Name(n) OBJECT_ID() OBJECT_Name() 典型使用场景：因动态串接SQL语句而需要获取Schema信息时 系统表sysobjects中的id字段就是object id 数学函数 ABS，SQRT，SIN，COS… PI() ROUND/FLOOR/CEILING 小心负数！ 汇总计算函数 SUM…Group By… Count MIN，MAX，AVG 字符串函数 ASCII/CHAR/STR, Unicode/NChar CharIndex, PatIndex SubString, Left, Right LTrim, RTrim Len Lower, Upper Replace, Stuff Reverse 日期时间函数 •DateAdd §参数可为负数，所以没有DateDec函数！ •DateDiff •DatePart •DateName •GetDate, GetUTCDate •Year, Month, Day §参数0表示1900/1/1，即Year(0) = 1900 •如何正确约束datetime范围 §小心时分秒 §典型例子：查询某个月份的数据 ≋>=当月1日 and <次月首日 数据类型转换函数 •CAST…AS §CAST(expression AS type) §例子： CAST(title AS char(50)) •CONVERT §Convert(type, expression) §例子：CONVERT(char(50), title) •两者等效 §Use CAST rather than CONVERT if you want Transact-SQL program code to comply with SQL-92. §Use CONVERT rather than CAST to take advantage of the style functionality in CONVERT. •Style参数 §The style parameter of CONVERT provides a variety of date display formats when converting datetime data to char or varchar. §Select CONVERT(char(12), GETDATE(), 3) ≋     This statement converts the current date to style 3, dd/mm/yy. 其他T-SQL语句或元素 AS 别名 同一表被多次使用 ≋典型用例：查询以月份为Column的销售报表 查询中间结果作为FROM子句元素 ≋Select …FROM (Select…) AS Q1 BULK Insert 类似BCP命令行的功能 Union vs Union All 前者相当于后者加上DISTINCT的效果 SQL SERVER 的函数 1.字符串函数 长度与分析用 datalength(Char_expr) 返回字符串包含字符数,但不包含后面的空格 substring(expression,start,length) 不多说了,取子串 right(char_expr,int_expr) 返回字符串右边int_expr个字符 字符操作类 upper(char_expr) 转为大写 lower(char_expr) 转为小写 space(int_expr) 生成int_expr个空格 replicate(char_expr,int_expr)复制字符串int_expr次 reverse(char_expr) 反转字符串 stuff(char_expr1,start,length,char_expr2) 将字符串char_expr1中的从 start开始的length个字符用char_expr2代替 ltrim(char_expr) rtrim(char_expr) 取掉空格 ascii(char) char(ascii) 两函数对应,取ascii码,根据ascii吗取字符 字符串查找 charindex(char_expr,expression) 返回char_expr的起始位置 patindex("%pattern%",expression) 返回指定模式的起始位置,否则为0 2.数学函数 abs(numeric_expr) 求绝对值 ceiling(numeric_expr) 取大于等于指定值的最小整数 exp(float_expr) 取指数 floor(numeric_expr) 小于等于指定值得最大整数 pi() 3.1415926......... power(numeric_expr,power) 返回power次方 rand([int_expr]) 随机数产生器 round(numeric_expr,int_expr) 安int_expr规定的精度四舍五入 sign(int_expr) 根据正数,0,负数,,返回+1,0,-1 sqrt(float_expr) 平方根 3.日期函数 getdate() 返回日期 datename(datepart,date_expr) 返回名称如 June datepart(datepart,date_expr) 取日期一部份 datediff(datepart,date_expr1.dateexpr2) 日期差 dateadd(datepart,number,date_expr) 返回日期加上 number 上述函数中datepart的 写法 取值和意义 yy 1753-9999 年份 qq 1-4 刻 mm 1-12 月 dy 1-366 日 dd 1-31 日 wk 1-54 周 dw 1-7 周几 hh 0-23 小时 mi 0-59 分钟 ss 0-59 秒 ms 0-999 毫秒 日期转换 convert() 4.系统函数 suser_name() 用户登录名 user_name() 用户在数据库中的名字 user 用户在数据库中的名字 show_role() 对当前用户起作用的规则 db_name() 数据库名 object_name(obj_id) 数据库对象名 col_name(obj_id,col_id) 列名 col_length(objname,colname) 列长度 valid_name(char_expr) 是否是有效标识符