本文共 7647 字，大约阅读时间需要 25 分钟。
从网上收集，有些已经在2008不支持或者后续版本不支持，所以需要慎用。
XP_FileExist：用法：EXEC xp_fileexist[, 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 [
sp_detach_db sp_setnetnamesp_dropdevice sp_settriggerordersp_dropextendedproc sp_spaceusedsp_dropextendedproperty sp_tableoptionsp_dropmessage sp_unbindefaultsp_droptype sp_unbindrulesp_executesql sp_updateextendedpropertysp_getapplock sp_updatestatssp_getbindtoken sp_validnamesp_help sp_whoWeb 助手过程 sp_dropwebtasksp_makewebtasksp_enumcodepagessp_runwebtaskXML 过程 sp_xml_preparedocumentsp_xml_removedocument常规扩展过程 xp_cmdshellxp_logininfoxp_enumgroupsxp_msverxp_findnextmsgxp_revokeloginxp_grantloginxp_sprintfxp_logeventxp_sqlmaintxp_loginconfigxp_sscanfAPI 系统存储过程用户在 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_cursorsp_cursorclosesp_cursorexecutesp_cursorfetchsp_cursoropensp_cursoroptionsp_cursorpreparesp_cursorunprepare 这些系统存储过程支持 ADO、OLE DB 和 ODBC 中用于执行 Transact-SQL 语句的比较/执行模型：sp_executesp_preparesp_unpreparesp_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_cmdshellSQL Server 2005中xp_cmdshell默认是禁用的，通过SQL Server Surface Area Configuration进行设定（~ for features）xp_availablemediaxp_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…CountMIN，MAX，AVG字符串函数ASCII/CHAR/STR, Unicode/NCharCharIndex, PatIndexSubString, Left, RightLTrim, RTrimLenLower, UpperReplace, StuffReverse日期时间函数•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 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 take advantage of the style functionality in convert.•style参数§the parameter provides a variety date display formats when converting datetime data char or varchar.§select convert(char(12), getdate(), 3)≋ this statement converts current 3, dd mm yy.其他t-sql语句或元素as 别名同一表被多次使用≋典型用例：查询以月份为column的销售报表查询中间结果作为from子句元素≋select …from (select…) q1bulk 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) 返回指定模式的起始位置,否则为02.数学函数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,-1sqrt(float_expr) 平方根3.日期函数getdate() 返回日期datename(datepart,date_expr) 返回名称如 junedatepart(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 秒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.columnswhere table_name = N'表名'--7:获取数据库文件路径select ltrim(rtrim(filename)) from 数据库名..sysfiles where charindex('MDF',filename)>0orselect 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 intdeclare @now datetimeselect @now = getdate()DECLARE sp_cursor CURSOR FORSELECT object_name(id)FROM sysobjectsWHERE xtype = 'P'AND type = 'P'AND crdate < @nowAND OBJECTPROPERTY(id, 'IsMSShipped')=0OPEN sp_cursorFETCH NEXT FROM sp_cursorINTO @sp_nameWHILE @@FETCH_STATUS = 0BEGINSELECT @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_nameEXEC sp_executesql @sp_contentFETCH NEXT FROM sp_cursorINTO @sp_nameENDCLOSE sp_cursorDEALLOCATE sp_cursor该存储过程利用了 sysobjects 和 syscomments 表，并巧妙地修改了原存储过程的 SQL 定义语句，将 AS 修改为了 WITH ENCRYPTION AS，从而达到了加密存储过程的目的。本存储过程在 SQL Server 2000 上通过。
转载地址：http://sapox.baihongyu.com/