Tag: 空间预览模式: 普通 | 列表

查看MSSQLServer数据库空间使用情况的存储过程

作者:admin  日期:2010-03-14

SQL代码
  1. Create procedure SpaceUsed  
  2. as  
  3. begin  
  4. declare @id int -- The object id of @objname.  
  5. declare @type character(2) -- The object type.  
  6. declare @pages int -- Working variable for size calc.  
  7. declare @dbname sysname  
  8. declare @dbsize dec(15,0)  
  9. declare @logsize dec(15)  
  10. declare @bytesperpage dec(15,0)  
  11. declare @pagesperMB dec(15,0)  
  12. declare @objname nvarchar(776) -- The object we want size on.  
  13. declare @updateusage varchar(5) -- Param. for specifying that  
  14. create table #temp1  
  15. (  
  16. 表名 varchar(200) null,  
  17. 行数 char(11) null,  
  18. 保留空间 varchar(15) null,  
  19. 数据使用空间 varchar(15) null,  
  20. 索引使用空间 varchar(15) null,  
  21. 未用空间 varchar(15) null  
  22. )  
  23. --select @objname='N_dep' -- usage info. should be updated.  
  24. select @updateusage='false'  
  25. /*Create temp tables before any DML to ensure dynamic  
  26. ** We need to create a temp table to do the calculation.  
  27. ** reserved: sum(reserved) where indid in (0, 1, 255)  
  28. ** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)  
  29. ** indexp: sum(used) where indid in (0, 1, 255) - data  
  30. ** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)  
  31. */  
  32. declare cur_table cursor for  
  33. select name from sysobjects where type='u'  
  34. Open cur_table  
  35. fetch next from cur_table into @objname  
  36. While @@FETCH_STATUS=0  
  37. begin  
  38. create table #spt_space  
  39. (  
  40. rows int null,  
  41. reserved dec(15) null,  
  42. data dec(15) null,  
  43. indexp dec(15) null,  
  44. unused dec(15) null  
  45. )  
  46. /*  
  47. ** Check to see if user wants usages updated.  
  48. */  
  49. if @updateusage is not null  
  50. begin  
  51. select @updateusage=lower(@updateusage)  
  52. if @updateusage not in ('true','false')  
  53. begin  
  54. raiserror(15143,-1,-1,@updateusage)  
  55. return(1)  
  56. end  
  57. end  
  58. /*  
  59. ** Check to see that the objname is local.  
  60. */  
  61. if @objname IS NOT NULL  
  62. begin  
  63. select @dbname = parsename(@objname, 3)  
  64. if @dbname is not null and @dbname <> db_name()  
  65. begin  
  66. raiserror(15250,-1,-1)  
  67. return (1)  
  68. end  
  69. if @dbname is null  
  70. select @dbname = db_name()  
  71. /*  
  72. ** Try to find the object.  
  73. */  
  74. select @id = null  
  75. select @id = id, @type = xtype  
  76. from sysobjects  
  77. where id = object_id(@objname)  
  78. /*  
  79. ** Does the object exist?  
  80. */  
  81. if @id is null  
  82. begin  
  83. raiserror(15009,-1,-1,@objname,@dbname)  
  84. return (1)  
  85. end  
  86. if not exists (select * from sysindexes  
  87. where @id = id and indid < 2)  
  88. if @type in ('P ','D ','R ','TR','C ','RF') --data stored in sysprocedures  
  89. begin  
  90. raiserror(15234,-1,-1)  
  91. return (1)  
  92. end  
  93. else if @type = 'V ' -- View => no physical data storage.  
  94. begin  
  95. raiserror(15235,-1,-1)  
  96. return (1)  
  97. end  
  98. else if @type in ('PK','UQ') -- no physical data storage. --?!?! too many similar messages  
  99. begin  
  100. raiserror(15064,-1,-1)  
  101. return (1)  
  102. end  
  103. else if @type = 'F ' -- FK => no physical data storage.  
  104. begin  
  105. raiserror(15275,-1,-1)  
  106. return (1)  
  107. end  
  108. end  
  109. /*  
  110. ** Update usages if user specified to do so.  
  111. */  
  112. if @updateusage = 'true'  
  113. begin  
  114. if @objname is null  
  115. dbcc updateusage(0) with no_infomsgs  
  116. else  
  117. dbcc updateusage(0,@objname) with no_infomsgs  
  118. print ' '  
  119. end  
  120. set nocount on  
  121. /*  
  122. ** If @id is null, then we want summary data.  
  123. */  
  124. /* Space used calculated in the following way  
  125. ** @dbsize = Pages used  
  126. ** @bytesperpage = d.low (where d = master.dbo.spt_values) is  
  127. ** the # of bytes per page when d.type = 'E' and  
  128. ** d.number = 1.  
  129. ** Size = @dbsize * d.low / (1048576 (OR 1 MB))  
  130. */  
  131. if @id is null  
  132. begin  
  133. select @dbsize = sum(convert(dec(15),size))  
  134. from dbo.sysfiles  
  135. where (status & 64 = 0)  
  136. select @logsize = sum(convert(dec(15),size))  
  137. from dbo.sysfiles  
  138. where (status & 64 <> 0)  
  139. select @bytesperpage = low  
  140. from master.dbo.spt_values  
  141. where number = 1  
  142. and type = 'E'  
  143. select @pagesperMB = 1048576 / @bytesperpage  
  144. select database_name = db_name(),  
  145. database_size =  
  146. ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'),  
  147. 'unallocated space' =  
  148. ltrim(str((@dbsize -  
  149. (select sum(convert(dec(15),reserved))  
  150. from sysindexes  
  151. where indid in (0, 1, 255)  
  152. )) / @pagesperMB,15,2)+ ' MB')  
  153. print ' '  
  154. /*  
  155. ** Now calculate the summary data.  
  156. ** reserved: sum(reserved) where indid in (0, 1, 255)  
  157. */  
  158. insert into #spt_space (reserved)  
  159. select sum(convert(dec(15),reserved))  
  160. from sysindexes  
  161. where indid in (0, 1, 255)  
  162. /*  
  163. ** data: sum(dpages) where indid < 2  
  164. ** + sum(used) where indid = 255 (text)  
  165. */  
  166. select @pages = sum(convert(dec(15),dpages))  
  167. from sysindexes  
  168. where indid < 2  
  169. select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)  
  170. from sysindexes  
  171. where indid = 255  
  172. update #spt_space  
  173. set data = @pages  
  174. /* index: sum(used) where indid in (0, 1, 255) - data */  
  175. update #spt_space  
  176. set indexp = (select sum(convert(dec(15),used))  
  177. from sysindexes  
  178. where indid in (0, 1, 255))  
  179. - data  
  180. /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */  
  181. update #spt_space  
  182. set unused = reserved  
  183. - (select sum(convert(dec(15),used))  
  184. from sysindexes  
  185. where indid in (0, 1, 255))  
  186. select reserved = ltrim(str(reserved * d.low / 1024.,15,0) +  
  187. ' ' + 'KB'),  
  188. data = ltrim(str(data * d.low / 1024.,15,0) +  
  189. ' ' + 'KB'),  
  190. index_size = ltrim(str(indexp * d.low / 1024.,15,0) +  
  191. ' ' + 'KB'),  
  192. unused = ltrim(str(unused * d.low / 1024.,15,0) +  
  193. ' ' + 'KB')  
  194. from #spt_space, master.dbo.spt_values d  
  195. where d.number = 1  
  196. and d.type = 'E'  
  197. end  
  198. /*  
  199. ** We want a particular object.  
  200. */  
  201. else  
  202. begin  
  203. /*  
  204. ** Now calculate the summary data.  
  205. ** reserved: sum(reserved) where indid in (0, 1, 255)  
  206. */  
  207. insert into #spt_space (reserved)  
  208. select sum(reserved)  
  209. from sysindexes  
  210. where indid in (0, 1, 255)  
  211. and id = @id  
  212. /*  
  213. ** data: sum(dpages) where indid < 2  
  214. ** + sum(used) where indid = 255 (text)  
  215. */  
  216. select @pages = sum(dpages)  
  217. from sysindexes  
  218. where indid < 2  
  219. and id = @id  
  220. select @pages = @pages + isnull(sum(used), 0)  
  221. from sysindexes  
  222. where indid = 255  
  223. and id = @id  
  224. update #spt_space  
  225. set data = @pages  
  226. /* index: sum(used) where indid in (0, 1, 255) - data */  
  227. update #spt_space  
  228. set indexp = (select sum(used)  
  229. from sysindexes  
  230. where indid in (0, 1, 255)  
  231. and id = @id)  
  232. - data  
  233. /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */  
  234. update #spt_space  
  235. set unused = reserved  
  236. - (select sum(used)  
  237. from sysindexes  
  238. where indid in (0, 1, 255)  
  239. and id = @id)  
  240. update #spt_space  
  241. set rows = i.rows  
  242. from sysindexes i  
  243. where i.indid < 2  
  244. and i.id = @id  
  245. insert into #temp1  
  246. select name = object_name(@id),  
  247. rows = convert(char(11), rows),  
  248. reserved = ltrim(str(reserved * d.low / 1024.,15,0) +  
  249. ' ' + 'KB'),  
  250. data = ltrim(str(data * d.low / 1024.,15,0) +  
  251. ' ' + 'KB'),  
  252. index_size = ltrim(str(indexp * d.low / 1024.,15,0) +  
  253. ' ' + 'KB'),  
  254. unused = ltrim(str(unused * d.low / 1024.,15,0) +  
  255. ' ' + 'KB')  
  256. from #spt_space, master.dbo.spt_values d  
  257. where d.number = 1  
  258. and d.type = 'E'  
  259. Drop table #spt_space  
  260. end  
  261. fetch next from cur_table into @objname  
  262. end  
  263. Close cur_table  
  264. DEALLOCATE cur_table  
  265. Select * from #temp1 order by len(数据使用空间) desc,数据使用空间 desc,保留空间 desc  
  266. Drop table #temp1  
  267. return (0)  
  268. end  
  269. GO   
  270. 详细出处参考：http://www.jb51.net/article/6788.htm  

Tags: SQL 存储过程 空间

分类:MSSQL | 固定链接 | 评论: 0 | 引用: 0 | 查看次数: 74