sql server 資料庫日常維護，管理，巡檢過程中你可能經常需要用到一些sql語句（亦或方法）來檢視資料庫伺服器環境（作業系統版本, 磁盤空間，cpu，ram資訊），資料庫資訊（資料庫版本，執行個體名稱...）,資料庫對象等。
檢視資料庫資訊
檢視資料庫伺服器名稱
方法1：sql腳本查詢，可以通過下面腳本來查詢。
預設執行個體
預設執行個體查詢
select @@servername as servername;
select serverproperty('servername') as servername;
select srvname as servername from sys.sysservers;
select serverproperty('machinename') as servername
命名執行個體
命名執行個體查詢
select substring(@@servername, 0, charindex('\', @@servername))as servername;
select substring(convert(varchar(100),serverproperty('servername')), 0, charindex('\',convert(varchar(100),serverproperty('servername')))) as servername;
select substring(srvname, 0, charindex('\', srvname)) as servername from sys.sysservers;
方法2：在資料庫執行個體單擊右鍵，選擇“屬性”——》“正常”選項裡面，你可以看到伺服器名稱（劃紅線部分）
方法3：都不好意思說了，你懂的。
檢視資料庫執行個體名稱
方法1：去服務（services.msc）裡面查找sql server（××××）這樣的服務，有多少個就就有多少資料庫執行個體，一般預設執行個體是sql server (mssqlserver)
方法2：去sql配置管理器的sql server服務配置裡面找上面描述的服務。
方法3：腳本查詢，隻是截取資料庫服務名稱的執行個體名（其實這個還真沒必要，通過上面的腳本就可檢視執行個體，注意預設執行個體）
code snippet
select @@servicename as instantname;
select isnull(serverproperty('instancename'),'mssqlserver') as instancename;
下面腳本僅對命名執行個體有效，預設執行個體查詢處理的是計算機名稱
select substring(@@servername,charindex('\', @@servername)+1,100) as instantname;
select substring(srvname, charindex('\', srvname) +1, 100) as instantname from sys.sysservers;
檢視資料庫版本号
方法1：sql 1：
select serverproperty('productversion') as productversion ,
serverproperty('productlevel') as productlevel ,
serverproperty('edition') as edition
方法2：sql 2： 看起來比較麻煩
select @@version as product_version;
microsoft sql server 2005 - 9.00.4035.00 (intel x86) nov 24 2008 13:01:59
copyright (c) 1988-2005 microsoft corporation
standard edition on windows nt 5.2 (build 3790: service pack 2)
如何從 9.00.4035.00 (intel x86) 讀取一些資訊呢？
第一個數字：8 代表2000版本， 9 代表2005版本， 10 代表2008 版本
第二個數字：50 代表r2版本， 00 代表非r2版本
第三個數字：如上所示4035中第一個數字4代表打了更新檔sp3，其規則如下所示
1： 代表沒有打更新檔
2： 代表打了sp1更新檔
3： 代表打了sp2更新檔，以此類推
035 ： 代表相關的hotfix版本，我們在進行更新，備份,恢複一定要看清楚這個版本，隻有在同一版本下的相關資料才能進行恢複（同版本備份檔案），這點要注意。
方法3：在資料庫執行個體上單擊右鍵，選擇“屬性”——》“正常”選項裡面，你可以看到伺服器名稱，平台，作業系統,資料庫版本資訊。
檢視資料庫已經打的更新檔
方法1：如上所示，可以通過9.00.4035.00 (intel x86) 來确定已經資料庫已經打的最新更新檔sp3。
方法2：在資料庫執行個體上單擊右鍵，選擇“屬性”——》“正常”選項裡面，可以通過看到的版本資訊檢視更新檔
方法3：在sql配置管理器,找到相應的執行個體的sql server服務,單擊右鍵屬性.
檢視執行個體資料庫的相關資訊
檢視執行個體有哪些資料庫，建立時間、排序規則.......
方法1：sql 查詢. 其實在視圖sys.databases裡面你可以檢視很多關于資料庫的資訊，例如，資料庫使用者通路設定,資料庫的狀态......
select * from sys.databases
方法2: 在mircrosoft sql server management studio管理器裡面檢視。
檢視排序規則資訊
1：檢視執行個體排序規則
select serverproperty(n'collation')
2：檢視資料庫排序規則
select name, collation_name from sys.databases
查詢目前資料庫的磁盤使用情況
如需要查詢其他資料庫，則需在前面指定資料庫名稱
exec sp_spaceused;
檢視資料庫啟動相關參數
exec sp_configure;
檢視資料庫啟動時間
方法1：
select convert(varchar(30), login_time,120) as startdatetime
from master..sysprocesses where spid=1
檢視所有資料庫名稱及大小
exec sp_helpdb;
方法2：
selectdatabase_id as databaseid ,
db_name(database_id) as databasename ,
cast(sum(size) * 8.0 / 1024 as decimal(8, 4)) as [size(mb)]
fromsys.master_files
group by database_id
檢視所有資料庫使用者登入資訊
exec sp_helplogins;
檢視所有資料庫使用者所屬的角色資訊
exec sp_helpsrvrolemember
更改某個資料對象的使用者屬主
sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner'
注意：更改對象名的任一部分都可能破壞腳本和存儲過程。把一台伺服器上的資料庫使用者登入資訊備份出來可以用add_login_to_aserver腳本。檢視某資料庫下,對象級使用者權限sp_helprotect
檢視連結伺服器
exec sp_helplinkedsrvlogin
檢視遠端資料庫使用者登入資訊
sp_helpremotelogin
檢視資料庫下某個資料對象的大小
sp_spaceused @objname
檢視某資料庫下某個資料對象的索引資訊
sp_helpindex @objname
檢視某資料庫下某個資料對象的的限制資訊
sp_helpconstraint @objname
檢視表的相關資訊
sp_help 'table_name'
修複遷移伺服器時孤立使用者時
use {目标資料庫}
exec sp_change_users_login 'update_one', '{目标資料庫已存在的使用者名}', '{建立的登入使用者名}'
方法2
declare @cmd nvarchar(4000)
set @cmd = n'exec [?].sys.sp_change_users_login @action = ''auto_fix''
, @usernamepattern = ''qa''
, @loginname = null
, @password = ''abc'' '
exec sp_msforeachdb@cmd
檢視資料庫資料檔案情況
檢視資料庫執行個體各個資料庫的資料檔案資訊
方法1： 選擇某個資料庫，然後單擊右鍵屬性...(後面我就不說了，不知道的自己百度)
方法2：sql
select database_id as databaseid ,
db_name(database_id) as databasename ,
name as logicalname ,
type_desc as filetypedesc ,
physical_name as physicalname ,
state_desc as statedesc ,
case when max_size = 0 then n'不允許增長'
when max_size = -1 then n'自動增長'
else ltrim(str(max_size * 8.0 / 1024 / 1024, 14, 2)) + 'g'
end as maxsize ,
case when is_percent_growth = 1
then rtrim(cast(growth as char(10))) + '%'
else rtrim(cast(growth as char(10))) + 'm'
end as growth ,
is_read_only as isreadonly ,
is_percent_growth as ispercentgrowth ,
cast(size * 8.0 / 1024 / 1024 as decimal(8, 4)) as [size(gb)]
from sys.master_files
檢視單個資料庫的資料檔案資訊：
sql 1：上面sql加上查詢條件
sql 2：
select name as databasename ,
physical_name as physicalname ,
type_desc as filetypedesc ,
state_desc as statedesc ,
(( size * 8.0 ) / 1024 / 1024 ) as [size(gb)] ,
case when max_size = 0 then n'不允許增長'
when max_size = -1 then n'自動增長'
else ltrim(str(max_size * 8.0 / 1024 / 1024, 14, 2)) + 'g'
end as maxsize ,
case when is_percent_growth = 1
then rtrim(cast(growth as char(10))) + '%'
else rtrim(cast(growth as char(10))) + 'm'
end as growth ,
is_read_only as isreadonly ,
is_percent_growth as ispercentgrowth ,
cast(size * 8.0 / 1024 / 1024 as decimal(8, 4)) as [size(gb)]
from sys.database_files ;
sql 3：
select fileid as fileid ,
groupid as groupid ,
size as databasesize ,
growth as growth ,
perf as perf ,
name as name ,
filename as filename
from mesdb.dbo.sysfiles ;
檢視資料庫伺服器各資料庫日志檔案的大小及使用率/狀态
dbcc sqlperf(logspace)
檢視目前資料庫的檔案狀态
exec ('dbcc showfilestats')
檢視資料庫存儲過程
檢視有哪些存儲過程
exec sp_stored_procedures;
select * from sys.procedures;
方法3：
select * from sys.sysobjects where xtype='p';
檢視存儲過程基本資訊
exec sp_help 'dbo.sp_who_lock'
檢視存儲過程源代碼：
exec sp_helptext 'procedurename'
select *
from sys.sql_modules
where object_id = object_id(n'procedurename')
select s.text as proceduretext ,
s.encrypted as encrypted ,
s.number as number ,
convert(nchar(2), o.xtype) as xtype ,
datalength(s.text) as procedurelen
fromdbo.syscomments s ,
dbo.sysobjects o
whereo.id = s.id
and s.id = object_id(n'procedurename')
order by s.number ,
s.colid
option ( robust plan )
伺服器環境資訊
檢視資料庫所在機器作業系統參數
方法1：
exec master..xp_msver
詳解：xp_msver傳回有關 microsoft sql server 的版本資訊。xp_msver 還傳回有關伺服器的實際内部版本号的資訊以及伺服器環境的有關資訊，例如處理器類型(不能擷取具體型号)， ram 的容量等等。用腳本基本上很難擷取詳細的硬體資訊。
方法2：登入伺服器，直接檢視伺服器資訊。
檢視資料庫伺服器磁盤分區剩餘空間。
exec master.dbo.xp_fixeddrives;
方法2：登入伺服器直接檢視或用工具
方法3：請看ms sql 監控磁盤空間告警裡面介紹的方法
檢視資料庫伺服器磁盤容量資訊
檢視資料庫伺服器cpu/記憶體的大概資訊
select cpu_count as [logical cpu count] ,
hyperthread_ratio as [hyperthread ratio] ,
cpu_count / hyperthread_ratio as [physical cpu count],
physical_memory_in_bytes / 1048576 as [physical memory (mb)] ,
sqlserver_start_time
fromsys.dm_os_sys_info
option ( recompile ) ;
小結：用sql檢視伺服器硬體資訊，似乎不是個好主意，很難得到精确地資訊，例如cpu型号、記憶體條的頻率