原文:
微軟未公開存儲過程及有用的函數
從網上收集，有些已經在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 上通過。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
