點選下載詳細的說明查詢sqlserver資料庫及各表格空間利用情況
查詢sqlserver資料庫及各表格空間利用情況
1、exec sp_spaceused 查詢資料庫空間利用情況
2、exec sp_spaceused 'byscurrent' //查詢表格的空間利用情況
篇主要實現檢視sql server中每個表佔用的空間大小以及資料庫的使用情況。在學習遊標的也可以看看，裡邊用到了遊標。
實現思路為：
1.找到庫中所有的表
2.遍歷表名，如果是使用者表的話。執行儲存過程 sp_spaceused 結果如下：
3.執行儲存過程sp_executesql，將每個表的資訊插入結果儲存表 tablespaceinfo.
4.4.執行查詢，得到我們想要的結果。
整個程式為：
--判斷是否存在結果儲存表[tablespaceinfo]。如果不存在，則建立
if not exists
(
select * from dbo.sysobjects where id = object_id(N'[dbo].[tablespaceinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
)
create table tablespaceinfo
(
nameinfo varchar(50), --表的名字
rowsinfo int , --表的行數
reserved varchar(20) , --表保留的行數
datainfo varchar(20) , --資料量大小
index_size varchar(20) , --索引大小
unused varchar(20) --沒有被佔用的空間
)
delete from tablespaceinfo --清空原有資料表
declare @tablename varchar(255) --表名稱 變數
--申明遊標 取出庫中所有表名
DECLARE Info_cursor CURSOR FOR
select o.name from dbo.sysobjects as o where OBJECTPROPERTY(o.id, N'IsTable') = 1 and o.name not like N'#%%' order by o.name
OPEN Info_cursor
FETCH NEXT FROM Info_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
--如果是使用者表
if exists
(
select * from dbo.sysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1
)
--說明：sp_executesql 執行可以多次重複使用或動態生成的Transact-SQL 語句或批處理
-- sp_spaceused 顯示行數、保留的磁碟空間以及當前資料庫中的表、索引檢視
-- 或Service Broker 佇列所使用的磁碟空間，或顯示由整個資料庫保留和使用的磁碟空間。
--execute sp_executesql N'insert into tablespaceinfo exec sp_spaceused @tbname',N'@tbname varchar(255)',@tbname = @tablename
insert into tablespaceinfo exec sp_spaceused @tablename
-- 兩種方法是一樣的，理論上前者更快
FETCH NEXT FROM Info_cursor
INTO @tablename
END
CLOSE Info_cursor --閉關遊標
DEALLOCATE Info_cursor --釋放遊標
GO
--顯示資料庫資訊
sp_spaceused @updateusage = 'TRUE'
--顯示錶資訊
select * from tablespaceinfo order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc
--N的作用就是如果你的作業系統是簡體中文，你在N後面的單引號裡輸入了日本、德文、法文什麼的，能保證你的字串是正確的，這時N就要必寫；
--反之如果N後面的單引號裡只有英文和簡體中文(長度不能超過十幾K)，那這個N寫與不寫是一樣的。