--1. 查看數據庫的版本
select @@version Microsoft SQL Server 2005 - 9.00.3042.00
(Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005
Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
*/
--2. 查看數據庫所在機器操作系統參數
exec master..xp_msver Index Name Internal_Value Character_Value
1 ProductName NULL Microsoft SQL Server
2 ProductVersion 589824 9.00.3042.00
3 Language 1033 英語(美國)
4 Platform NULL NT INTEL X86
5 Comments NULL NT INTEL X86
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT
8 FileVersion NULL 2005.090.3042.00
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL © Microsoft Corp. All rights reserved.
11 LegalTrademarks NULL Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 199360512 NULL
15 WindowsVersion 170393861 5.1 (2600)
16 ProcessorCount 1 1
17 ProcessorActiveMask 1 00000001
18 ProcessorType 586 PROCESSOR_INTEL_PENTIUM
19 PhysicalMemory 1270 1270 (1332133888)
20 Product ID NULL NULL
*/
--3. 查看數據庫啟動的參數
sp_configure name minimum maximum config_value run_value
Ad Hoc Distributed QuerIEs 0 1 1 1
affinity I/O mask -2147483648 2147483647 0 0
affinity mask -2147483648 2147483647 0 0
Agent XPs 0 1 0 0
allow updates 0 1 0 0
awe enabled 0 1 0 0
blocked process threshold 0 86400 0 0
c2 audit mode 0 1 0 0
clr enabled 0 1 0 0
cost threshold for parallelism 0 32767 5 5
cross db ownership chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
Database Mail XPs 0 1 0 0
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
default trace enabled 0 1 1 1
disallow results from triggers 0 1 0 0
fill factor (%) 0 100 0 0
ft crawl bandwidth (max) 0 32767 100 100
ft crawl bandwidth (min) 0 32767 0 0
ft notify bandwidth (max) 0 32767 100 100
ft notify bandwidth (min) 0 32767 0 0
index create memory (KB) 704 2147483647 0 0
in-doubt xact resolution 0 2 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 64 0 0
max full-text crawl range 0 256 4 4
max server memory (MB) 16 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 128 32767 0 0
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 8
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
Ole Automation Procedures 0 1 0 0
open objects 0 2147483647 0 0
PH timeout (s) 1 3600 60 60
precompute rank 0 1 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote Access 0 1 1 1
remote admin connections 0 1 0 0
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
Replication XPs 0 1 0 0
scan for startup procs 0 1 0 0
server trigger recursion 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
SMO and DMO XPs 0 1 1 1
SQL Mail XPs 0 1 0 0
transform noise Words 0 1 0 0
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user instance timeout 5 65535 60 60
user instances enabled 0 1 1 1
user options 0 32767 0 0
Web Assistant Procedures 0 1 0 0
xp_cmdshell 0 1 0 0 --4. 查看數據庫啟動時間
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1 2009-02-09 18:25:10
*/ --5. 查看數據庫服務器名和實例名
print 'Server Name...............：' + convert(varchar(30),@@SERVERNAME)
--Server Name...............：CLARO\SQLEXPRESS
print 'Instance..................：' + convert(varchar(30),@@SERVICENAME)
--Instance..................：SQLEXPRESS
--6. 查看所有數據庫名稱及大小
sp_helpdb name db_size owner dbid created status compatibility_level
master 5.25 MB sa 1 Apr 8 2003 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=Chinese_PRC_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics 90
model 2.94 MB sa 3 Apr 8 2003 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=Chinese_PRC_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics 90
msdb 7.44 MB sa 4 Oct 14 2005 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=Chinese_PRC_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 90
tempdb 2.69 MB sa 2 Feb 9 2009 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=Chinese_PRC_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics 90
testdb 4.00 MB sa 5 Nov 12 2008 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=Chinese_PRC_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 90
*/ --7. 重命名數據庫SQL
sp_renamedb 'old_dbname', 'new_dbname' --8. 查看所有數據庫用戶登錄信息
sp_helplogins --9. 查看所有數據庫用戶所屬的角色信息
sp_helpsrvrolemember ServerRole MemberName MemberSID
sysadmin sa 0x01
sysadmin BUILTIN\Administrators 0x01020000000000052000000020020000
sysadmin NT AUTHORITY\SYSTEM 0x010100000000000512000000
sysadmin CLARO\SQLServer2005MSSQLUser$CLARO$SQLEXPRESS 0x010500000000000515000000A1F40462507B9E5F07E53B2BF3030000
*/ --10. 修復遷移服務器時孤立用戶時,可以用的fix_orphan_user腳本或者LoneUser過程 sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner' --把一台服務器上的數據庫用戶登錄信息備份出來可以用add_login_to_aserver腳本 --11. 查看某數據庫下,對象級用戶權限
sp_helprotect (1664 row(s) affected)
*/ --12. 查看鏈接服務器
sp_helplinkedsrvlogin Linked Server Local Login Is Self Mapping Remote Login
CLARO\SQLEXPRESS NULL 1 NULL
*/ --13. 查看遠端數據庫用戶登錄信息
sp_helpremotelogin Msg 15200, Level 16, State 1, Procedure sp_helpremotelogin, Line 14
There are no remote servers defined.
*/ --14. 查看某數據庫下某個數據對象的大小
sp_spaceused @objname
--還可以用sp_toptables過程看最大的N(默認為50)個表 --15. 查看某數據庫下某個數據對象的索引信息
sp_helpindex @objname
--還可以用SP_NChelpindex過程查看更詳細的索引情況
--SP_NChelpindex @objname
--clustered索引是把記錄按物理順序排列的，索引占的空間比較少。
--對鍵值DML操作十分頻繁的表我建議用非clustered索引和約束，fillfactor參數都用默認值。 --16. 查看某數據庫下某個數據對象的的約束信息
sp_helpconstraint @objname --17. 查看數據庫裡所有的存儲過程和函數
use @database_name
sp_stored_procedures --18. 查看存儲過程和函數的源代碼
sp_helptext '@procedure_name'
/*--example:
sp_helptext 'sp_MScleanupmergepublisher'
--create procedure dbo.sp_MScleanupmergepublisher
--as
-- exec sys.sp_MScleanupmergepublisher_internal
*/ --19. 查看包含某個字符串@str的數據對象名稱
select distinct object_name(id) from syscomments where text like '%@str%'
--創建加密的存儲過程或函數在AS前面加WITH ENCRYPTION參數
--解密加密過的存儲過程和函數可以用sp_decrypt過程 --20. 查看數據庫裡用戶和進程的信息
sp_who --21. 查看SQL Server數據庫裡的活動用戶和進程的信息
sp_who 'active' --22. 查看SQL Server數據庫裡的鎖的情況
sp_lock
--進程號(1--50)是SQL Server系統內部用的,進程號大於50的才是用戶的連接進程.
--spid是進程編號,dbid是數據庫編號,objid是數據對象編號 --23. 查看進程正在執行的SQL語句 /?
dbcc inputbuffer ()
推薦大家用經過改進後的sp_who3過程可以直接看到進程運行的SQL語句
sp_who5 --24. 檢查死鎖用sp_who_lock過程 /?
sp_who_lock --25. 查看和收縮數據庫文件的方法 dbcc sqlperf(logspace) Database Name Log Size (MB) Log Space Used (%) Status
master 1.242188 44.33962 0
tempdb 1.492188 62.04189 0
model 0.7421875 43.15789 0
msdb 1.992188 40.78431 0
testdb 0.9921875 41.53543 0
*/ --如果某些文件較大，收縮簡單恢復模式數據庫文章，收縮後@database_name_log的大小單位為M
--backup log @database_name with no_log
--dbcc shrinkfile (@database_name_log, 5) --26. 分析SQL Server SQL 語句的方法：
set statistics time {on | off}
set statistics io {on | off} --在查詢分析器->查詢->顯示估計的評估計劃(D)-Ctrl-L 或者點擊工具欄裡的圖形 set showplan_all {on | off}
set showplan_text { on | off }
set statistics profile { on | off }
