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

概述SQLServer中的系统表,存储过程和函数的功能及应用 2009-05-31 15:17 sp_detach_db sp_setnetname sp_dropdevice sp_settriggerorder sp_dropextendedproc   sp_spaceused sp_dropextendedproperty sp_tableoption sp_dropmessage sp_un sqlServer中的系统表,存储过程和函数的功能及应用 2009-05-31 15:17

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中的系统表,存储过程和函数的功能及应用全部内容，希望文章能够帮你解决SQLServer中的系统表,存储过程和函数的功能及应用所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。