原文: 微軟未公開存儲過程及有用的函數

從網上收集，有些已經在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 上通過。


  

微軟未公開存儲過程及有用的函數