微软未公开存储过程及有用的函数

从网上收集，有些已经在2008不支持或者后续版本不支持，所以需要慎用。

XP_FileExist： 用法：EXEC xp_fileexist <filename> [, <file_exists INT> OUTPUT] 例子：exec master.dbo.xp_fileexist 'C:\temp' SP_MSForEachDb： 例子1：exec dbo.sp_MSforeachdb 'select ''?'', * from [?].INFORMATION_SCHEMA.TABLES where table_name like ''authors'' ' 例子2：exec dbo.sp_MSforeachdb 'DBCC SHRINKDATABASE(N''?'' )' SP_MSForEachTable： 例子：exec dbo.sp_msforeachtable 'delete test.dbo.[?]' SP_who2： sp_MSdependencies： 用法：exec dbo.sp_MSdependencies [<object name> ] , [<object type>], [<Flags>], [<objlist>] 例子1：exec dbo.sp_MSdependencies [<object name> ] , [<object type>], [<Flags>], [<objlist>] 例子2：exec sp_msdependencies 'dbo.titleview' 例子3：exec sp_msdependencies NULL, 2 例子4：exec sp_msdependencies 'titles', NULL, 0x401fd xp_dirtree: 例子：EXEC master..xp_dirtree 'C:\MSSQL7' xp_enum_oledb_providers： 例子：EXEC master..xp_enum_oledb_providers xp_enumcodepages：（2008已经没有） 例子：EXEC master..xp_enumcodepages xp_enumerrorlogs： EXEC master..xp_enumerrorlogs xp_enumgroups： EXEC master..xp_enumgroups xp_fixeddrives： EXEC master..xp_fixeddrives xp_getnetname： EXEC master..xp_getnetname xp_readerrorlog： EXEC master..xp_readerrorlog xp_regdeletekey： EXECUTE xp_regdeletekey [@rootkey=]'rootkey', [@key=]'key' xp_regdeletevalue： EXECUTE xp_regdeletevalue [@rootkey=]'rootkey', [@key=]'key', [@value_name=]'value_name' xp_regread： EXECUTE xp_regread [@rootkey=]'rootkey', [@key=]'key' [, [@value_name=]'value_name'] [, [@value=]@value OUTPUT] xp_regwrite： EXECUTE xp_regwrite [@rootkey=]'rootkey', [@key=]'key', [@value_name=]'value_name', [@type=]'type', [@value=]'value' xp_subdirs： EXEC master..xp_subdirs 'C:\MSSQL7'

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) 是否是有效标识符 --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, 1, @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 该存储过程利用了 sysobjects 和 syscomments 表，并巧妙地修改了原存储过程的 SQL 定义语句，将 AS 修改为了 WITH ENCRYPTION AS，从而达到了加密存储过程的目 的。本存储过程在 SQL Server 2000 上通过。