SQLServer中的系统表,存储过程和函数的功能及应用

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,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) 根据正数,负数,返回+1,-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) 是否是有效标识符 --1：获取当前数据库中的所有用户表 select Name from sysobjects where xtype='u' and status>=0 --2：获取某一个表的所有字段 select name from syscolumns where id=object_id(N'表名') --3：查看与某一个表相关的视图、存储过程、函数 select a.* from sysobjects a,syscomments b where a.id = b.id and b.text like N'%表名%' --4：查看当前数据库中所有存储过程 select name as 存储过程名称 from sysobjects where xtype='P' --5：查询用户创建的所有数据库 select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa') 或者 select dbid,name AS DB_NAME from master..sysdatabases where sid <> 0x01 --6：查询某一个表的字段和数据类型 select column_name,data_type from information_schema.columns where table_name = N'表名' --7:获取数据库文件路径 select ltrim(rtrim(filename)) from 数据库名..sysfiles where charindex('MDF',filename)>0 or select ltrim(rtrim(filename)) from 数据库名..sysfiles where charindex('LDF',filename)>0 --8:获取某一个表的基本信息 sp_MShelpcolumns N'表名' --9:获取某一个表的主键、外键信息 exec sp_pkeys N'表名' exec sp_fkeys??? N'表名' --10:判断某一个表是否存在某一列(字段) if exists(select 1 from syscolumns where id=object_id(N'表名) and name=N'字段') ????? print??? N'存在' else ????? print??? N'不存在' 下面给出了一个存储过程，它的作用是自动将当前数据库的用户存储过程加密。 DECLARE @sp_name nvarchar(400) DECLARE @sp_content nvarchar(2000) DECLARE @asbegin int declare @now datetime select @now = getdate() DECLARE sp_cursor CURSOR FOR SELECT object_name(id) FROM sysobjects WHERE xtype = 'P' AND type = 'P' AND crdate < @now AND OBJECTPROPERTY(id,'IsMSShipped')=0 OPEN sp_cursor FETCH NEXT FROM sp_cursor INTO @sp_name WHILE @@FETCH_STATUS = 0 BEGIN SELECT @sp_content = text FROM syscomments WHERE id = OBJECT_ID(@sp_name) SELECT @asbegin = PATINDEX ( '%AS' + char(13) + '%',@sp_content) SELECT @sp_content = SUBSTRING(@sp_content,@asbegin - 1) + ' WITH ENCRYPTION AS' + SUBSTRING (@sp_content,@asbegin+2,LEN(@sp_content)) SELECT @sp_name = 'DROP PROCEDURE [' + @sp_name + ']' EXEC sp_executesql @sp_name EXEC sp_executesql @sp_content FETCH NEXT FROM sp_cursor INTO @sp_name END CLOSE sp_cursor DEALLOCATE sp_cursor

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

SQLServer中的系统表,存储过程和函数的功能及应用 2009-05-31 15:17