`
wsql
  • 浏览: 11714576 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
文章分类
社区版块
存档分类
最新评论

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

 
阅读更多

从网上收集,有些已经在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 上通过。

分享到:
评论

相关推荐

    VB msvbvm60.dll中vbaCopyBytes vbaCopyBytesZero函数操作实例.rar

    分别取自微软的VB支持库 msvbvm60.dll 中,函数名为__vbaCopyBytes and __vbaCopyBytesZero.封装在VB msvbvm60.dll中的2个没有公开的内存操作函数示例。  vbaCopyBytesZero:该函数功能是从一个内存块(存储单元)中...

    C#微软培训资料

    10.3 构造函数和析构函数 .119 10.4 小 结 .122 第十一章 方 法 .124 11.1 方法的声明.124 11.2 方法中的参数.125 11.3 静态和非静态的方法.129 11.4 方法的重载.130 11.5 操作符重载.134 11.6 小 ...

    openssl-1.0.0a

    OpenSSL提供了产生各种公开密钥对和对称密钥的方法、函数和应用程序,同时提供了对公钥和私钥的DER编解码功能。并实现了私钥的PKCS#12和PKCS#8的编解码功能。OpenSSL在标准中提供了对私钥的加密保护功能,使得密钥...

    计算工厂 2

    (计算工厂 2的运行需要.NET Framework 2.0的支持, 请到微软官方网站下载) 计算工厂 2包含以下功能: 1. 直接输入表达式并求值,比如“1 + log(2)”。 2. 可在表达式中使用变量,比如“a + b / c”。在...

    Windows 内核情景分析--采用开源代码ReactOS (上册) part01

    全书从“内存管理”、“进程”、“进程间通信”、“设备驱动”等多个方面进行分析介绍,所有的分析都有ReactOS的源代码(以及部分由微软公开的源代码)作为依据,使读者能深入理解Windows内核的方方面面,也可以使...

    net学习笔记及其他代码应用

    public : 公共成员,完全公开,没有访问限制。 internal: 在同一命名空间内可以访问。 2 .列举ASP.NET 页面之间传递值的几种方式。 答. 1.使用QueryString, 如....?id=1; response. Redirect().... 2.使用...

    OpenSSL-1_0_0d_Win32

    OpenSSL提供了产生各种公开密钥对和对称密钥的方法、函数和应用程序,同时提供了对公钥和私钥的DER编解码功能。并实现了私钥的PKCS#12和PKCS#8的编解码功能。OpenSSL在标准中提供了对私钥的加密保护功能,使得密钥...

    WTL 9.1 5270 ReadMe 中文 汉化 中英文对照版 01d

    这些类并未使用hooks(钩子程序)或线程本地存储技术,所以没有植入技术改进的限制。它们也不存在内部依赖性,可以自由地和SDK代码一起混合使用。总之,WTL提供最小最高效的代码,非常接近SDK的体积和速度,同时为...

    WTL 9.1 5270 Beta 中文 汉化 中英文对照版

    这些类并未使用hooks(钩子程序)或线程本地存储技术,所以没有植入技术改进的限制。它们也不存在内部依赖性,可以自由地和SDK代码一起混合使用。总之,WTL提供最小最高效的代码,非常接近SDK的体积和速度,同时为...

    超级有影响力霸气的Java面试题大全文档

     JAVA平台提供了两个类:String和StringBuffer,它们可以储存和操作字符串,即包含多个字符的字符数据。这个String类提供了数值不可改变的字符串。而这个StringBuffer类提供的字符串进行修改。当你知道字符数据要...

    java 面试题 总结

    java编译器要求方法必须声明抛出可能发生的非运行时异常,但是并不要求必须声明抛出未被捕获的运行时异常。 6、说出Servlet的生命周期,并说出Servlet和CGI的区别。 Servlet被服务器实例化后,容器运行其init方法,...

    自己动手写操作系统(含源代码).part2

    我们有许多源代码公开的操作系统,可供随时下载和阅读,看上去好像让实现一个供自己把玩的微型操作系统变得容易很多,但事实往往不尽人意,因为这些代码动辄上万甚至几十几百万行,而且细节之间经常互相关联,要...

    自己动手写操作系统(含源代码).part1

    我们有许多源代码公开的操作系统,可供随时下载和阅读,看上去好像让实现一个供自己把玩的微型操作系统变得容易很多,但事实往往不尽人意,因为这些代码动辄上万甚至几十几百万行,而且细节之间经常互相关联,要...

Global site tag (gtag.js) - Google Analytics