SQL Server 管理常用的SQL和T-SQL
B4"8IhAB wA".Rlxw"D 2005-04 余楓
2 :t82] vBS$75<wI SQL Server 數(shù)據(jù)庫(kù)管理常用的SQL和T-SQL:
gM Y!Q P yOSb 5 1. 查看數(shù)據(jù)庫(kù)的版本
?C>"0kowS select @@version
]`AK`Db 5: g2F\'m 常見(jiàn)的幾種SQL SERVER打補(bǔ)丁后的版本號(hào):
tysX7sS6 E2-8#l\ 8.00.194 Microsoft SQL Server 2000
rfr+ ,(y\ 8.00.384 Microsoft SQL Server 2000 SP1
&8~G3n 8.00.532 Microsoft SQL Server 2000 SP2
R,Qu-lg0 8.00.760 Microsoft SQL Server 2000 SP3
*>o0nbW 8.00.818 Microsoft SQL Server 2000 SP3 w/ Cumulative Patch MS03-031
}`%wFS#o 8.00.2039 Microsoft SQL Server 2000 SP4
V %(_tO P{j==O[x% Microsoft SQL Server 2005
/+F9XqL2C Q}H}g#z', 9.00.1399.06 (Intel X86)
y2?m E 9.00.2047.00 (Intel X86)(Build 3790: Service Pack 1)
YabRAQ4 'c PuW 2. 查看數(shù)據(jù)庫(kù)所在機(jī)器操作系統(tǒng)參數(shù)
Aa*fsw L exec master..xp_msver
z~tYO zFS !S^ Z ]gz 3. 查看數(shù)據(jù)庫(kù)啟動(dòng)的參數(shù)
?zzZ]gR) sp_configure
Ak E*Iv lhm K$,s 4. 查看SQLSERVER2000數(shù)據(jù)庫(kù)啟動(dòng)時(shí)間
Wt7kqN 1~ select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
_G(~ ;z,~K=@ 查看SQLSERVER2005數(shù)據(jù)庫(kù)啟動(dòng)時(shí)間
&W ]{q.Ue select convert(varchar(30),start_time,120) from master.sys.dm_exec_requests where session_id=1
{e 6M! |dr7AQ? 查看數(shù)據(jù)庫(kù)服務(wù)器名和實(shí)例名
,X?u{, print 'Server Name...............: ' + convert(varchar(30),@@SERVERNAME)
(;nR&g print 'Instance..................: ' + convert(varchar(30),@@SERVICENAME)
q) J &l \_Y]bs(q 5. 查看所有數(shù)據(jù)庫(kù)名稱及大小
8GU9/9V \ sp_helpdb
*8u)tP m 4(cnd7 重命名數(shù)據(jù)庫(kù)用的SQL
,N0~v ur sp_renamedb 'old_dbname', 'new_dbname'
Cel KuhR t{Okb2t;a 6. 查看所有數(shù)據(jù)庫(kù)用戶登錄信息
}4 sp_helplogins
SX4+TIgV NN(&)1F[n 查看所有數(shù)據(jù)庫(kù)用戶所屬的角色信息
ZuGVnAUv sp_helpsrvrolemember
VEUvraGL0} T08_ BG9 修復(fù)遷移服務(wù)器時(shí)孤立用戶時(shí),可以用的LoneUser腳本或者fix_all_orphan_user過(guò)程
I")5AK{* .A"B+QZ w 更改某個(gè)數(shù)據(jù)對(duì)象的用戶屬主
&HDl sp_changeobjectowner [@objname=] 'object', [@newowner=] 'owner'
bC.i|5gdv $T/\#PV 注意: 更改對(duì)象名的任一部分都可能破壞腳本和存儲(chǔ)過(guò)程。
m[cRkR $ .^n 0JVK 把一臺(tái)服務(wù)器上的數(shù)據(jù)庫(kù)用戶登錄信息備份出來(lái)可以用add_login_to_aserver腳本
eQ b9 {dHN9"JrM 查看某數(shù)據(jù)庫(kù)下,對(duì)象級(jí)用戶權(quán)限
3$k9Gu A sp_helprotect
!`- )6<C+ 60Hr5[ 7. 查看鏈接服務(wù)器
jWtZ1J7z'( sp_helplinkedsrvlogin
{+@oE5/J/a c8*r9qtd) 查看遠(yuǎn)端數(shù)據(jù)庫(kù)用戶登錄信息
s: t[=G'# sp_helpremotelogin
Vl$.G. 0 xr|73w J]y 8.查看某數(shù)據(jù)庫(kù)下某個(gè)數(shù)據(jù)對(duì)象的大小
>.TjO(Q sp_spaceused @objname
F_uv= 0o ~ 8u="b?} 還可以用sp_toptables過(guò)程看最大的N(默認(rèn)為50)個(gè)表
$~zY 'u HaZrA ge 查看某數(shù)據(jù)庫(kù)下某個(gè)數(shù)據(jù)對(duì)象的索引信息
4:ltBoL sp_helpindex @objname
2~`ssYy!#s +Ikxgy7X 還可以用SP_NChelpindex過(guò)程查看更詳細(xì)的索引情況
Kl Cav~G SP_NChelpindex @objname
F{D]T(! >t u '2:lF- clustered索引是把記錄按物理順序排列的，索引占的空間比較少。
8^S.>&(fI 對(duì)鍵值DML操作十分頻繁的表我建議用非clustered索引和約束，fillfactor參數(shù)都用默認(rèn)值。
k>^E~#\@Y UY*6U 查看某數(shù)據(jù)庫(kù)下某個(gè)數(shù)據(jù)對(duì)象的的約束信息
, 8]uEDF sp_helpconstraint @objname
S<q\ zy qz`m<"#U 9.查看數(shù)據(jù)庫(kù)里所有的存儲(chǔ)過(guò)程和函數(shù)
+^?7-j$ use @database_name
nj_9 *nsp sp_stored_procedures
R2h:*Eh\! ib/IHfWY6 查看存儲(chǔ)過(guò)程和函數(shù)的源代碼
58($W@}}; sp_helptext '@procedure_name'
G'{2[~s ? (m{Q' dD. 查看包含某個(gè)字符串@str的數(shù)據(jù)對(duì)象名稱
?CN`xS | select distinct object_name(id) from syscomments where text like '%@str%'
0+p*|n ewdy6*Be1 創(chuàng)建加密的存儲(chǔ)過(guò)程或函數(shù)在AS前面加WITH ENCRYPTION參數(shù)
PI"(D >>A <c%GO $ 解密加密過(guò)的存儲(chǔ)過(guò)程和函數(shù)可以用sp_decrypt過(guò)程
NJYa"RbLi4 \/6 ;/ 10.查看數(shù)據(jù)庫(kù)里用戶和進(jìn)程的信息
]!*"98|m sp_who
2L@}mJ@_o },:AFb t 查看SQL Server數(shù)據(jù)庫(kù)里的活動(dòng)用戶和進(jìn)程的信息
p$q*Fj1<: sp_who 'active'
Li<m2MN@ ,%9aTJW; 查看SQL Server數(shù)據(jù)庫(kù)里的鎖的情況
lpt,E. sp_lock
B2gT(d pQ)M'@w1 進(jìn)程號(hào)1--50是SQL Server系統(tǒng)內(nèi)部用的,進(jìn)程號(hào)大于50的才是用戶的連接進(jìn)程.
%V}H' 4Z# E<?H3NL. spid是進(jìn)程編號(hào),dbid是數(shù)據(jù)庫(kù)編號(hào),objid是數(shù)據(jù)對(duì)象編號(hào)
> wa1Dz 8 _h9Y XTx 查看進(jìn)程正在執(zhí)行的SQL語(yǔ)句
>(El9@nQ4 dbcc inputbuffer ()
}t|ll 5+ d], &7 `c 推薦大家用經(jīng)過(guò)改進(jìn)后的sp_who3過(guò)程可以直接看到進(jìn)程運(yùn)行的SQL語(yǔ)句
k!nS-H{^ sp_who3
M=O5 YDDu s(L b0 檢查死鎖用sp_who_lock過(guò)程
~VuL4"N^` sp_who_lock
? \ 1r A*aRKVv1a 11.查看和收縮數(shù)據(jù)庫(kù)日志文件的方法
oa/<m[ZA M,TN ~ 查看所有數(shù)據(jù)庫(kù)日志文件大小
?. zl& dbcc sqlperf(logspace)
;}@M8Wc |PUp.[ @ 如果某些日志文件較大，收縮簡(jiǎn)單恢復(fù)模式數(shù)據(jù)庫(kù)日志，收縮后@database_name_log的大小單位為M
iaV;Tl^4+ backup log @database_name with no_log
UcjaC T- dbcc shrinkfile (@database_name_log, 5)
b#/.mO2SH V<73p s 12.分析SQL Server SQL 語(yǔ)句的方法:
YvDG 1A5J F_ G\wm) set statistics time {on | off}
M[a3 ZR Y%N% ]=( set statistics io {on | off}
/VD@J{`> I}%g5HP 圖形方式顯示查詢執(zhí)行計(jì)劃
0 1SN&U <W~q.(D 在查詢分析器->查詢->顯示估計(jì)的評(píng)估計(jì)劃(D)-Ctrl-L 或者點(diǎn)擊工具欄里的圖形
+;wb!HA "Z# +szGAM 文本方式顯示查詢執(zhí)行計(jì)劃
q rb:O5*,l m:?dkh3 set showplan_all {on | off}
6CWzKu:Jx pT: 8iJ set showplan_text { on | off }
/8>Rb<bX+d ~\i Xr3@\ set statistics profile { on | off }
j]53jn aH:a93:hW &d\)4mm:1 13.出現(xiàn)不一致錯(cuò)誤時(shí)，NT事件查看器里出3624號(hào)錯(cuò)誤，修復(fù)數(shù)據(jù)庫(kù)的方法
8k|[!_*"w- , IdB U8 先注釋掉應(yīng)用程序里引用的出現(xiàn)不一致性錯(cuò)誤的表，然后在備份或其它機(jī)器上先恢復(fù)然后做修復(fù)操作
*a$$ 9Z{w 3O2i]@PS alter database [@error_database_name] set single_user
v3F 4<mCgI X8FL < 修復(fù)出現(xiàn)不一致錯(cuò)誤的表
b:)|JPV5 =l "LWumb dbcc checktable('@error_table_name',repair_allow_data_loss)
^S}5'[o Uyl9V(r6 或者可惜選擇修復(fù)出現(xiàn)不一致錯(cuò)誤的小型數(shù)據(jù)庫(kù)名
[X Y2 4 po~.Oes_ dbcc checkdb('@error_database_name',repair_allow_data_loss)
nN3~1 ;=/ {wD`0yb5 alter database [@error_database_name] set multi_user
?o a~? a{" z@O\Mxt& CHECKDB 有3個(gè)參數(shù):
\NefID7wr ?R({8t4M repair_allow_data_loss 包括對(duì)行和頁(yè)進(jìn)行分配和取消分配以改正分配錯(cuò)誤、結(jié)構(gòu)行或頁(yè)的錯(cuò)誤，
@n*n 9s/ 以及刪除已損壞的文本對(duì)象，這些修復(fù)可能會(huì)導(dǎo)致一些數(shù)據(jù)丟失。
+ ^4RcIfM 修復(fù)操作可以在用戶事務(wù)下完成以允許用戶回滾所做的更改。
hgL_#3o 如果回滾修復(fù)，則數(shù)據(jù)庫(kù)仍會(huì)含有錯(cuò)誤，應(yīng)該從備份進(jìn)行恢復(fù)。
"N 如果由于所提供修復(fù)等級(jí)的緣故遺漏某個(gè)錯(cuò)誤的修復(fù)，則將遺漏任何取決于該修復(fù)的修復(fù)。
nK ;?&IH|\ 修復(fù)完成后，請(qǐng)備份數(shù)據(jù)庫(kù)。
qec*,vW5x, bOb<WdPqsA repair_fast 進(jìn)行小的、不耗時(shí)的修復(fù)操作，如修復(fù)非聚集索引中的附加鍵。
F(>>X# 這些修復(fù)可以很快完成，并且不會(huì)有丟失數(shù)據(jù)的危險(xiǎn)。
}7T0w4xeJ Z0bWQ^X repair_rebuild 執(zhí)行由 repair_fast 完成的所有修復(fù)，包括需要較長(zhǎng)時(shí)間的修復(fù)（如重建索引）。
SyVI3 4(k 執(zhí)行這些修復(fù)時(shí)不會(huì)有丟失數(shù)據(jù)的危險(xiǎn)。
t<r_m\B ---------------------------------------------------------------------------------------------------
^t^ +q : +m68:cf 有興趣還可以看看源自
www.sqlservercentral.com的檢查SQL Server全部信息的sqlserver_all_aspect腳本
[:wR?T!4 PA%'RH ---------------------------------------------------------------------------------------------------
v\!V4 W _;W2:y?Qo iL} q5_J 這些方法和腳本來(lái)源自SQL Server聯(lián)機(jī)叢書和
www.sqlservercentral.com, 我僅做了一些整理.
