热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

mysql不使用游标遍历_不用游标遍历数据库执行脚本

createtable#temp1([数据库名]varchar(50),[数据库大小(MB)]dec(15,2),[未分配空间(MB)]dec(15,2),[保留(MB)]dec(

create table#temp1

([数据库名] varchar(50),[数据库大小(MB)] dec (15,2),[未分配空间(MB)] dec (15,2),[保留(MB)] dec (15,2),[数据(MB)] dec (15,2),[索引(MB)] dec (15,2),[未使用(MB)] dec (15,2))insert into#temp1execsp_msforeachdb'use ?;

select

db_name(),

convert(dec (15,2),(convert(dec (15,2),dbsize) + convert (dec (15,2),logsize)) * 8192 / 1048576),

convert(dec (15,2),(case when dbsize >= reservedpages then (convert (dec (15,2),dbsize) - convert (dec (15,2),reservedpages)) * 8192 / 1048576 else 0 end)),

convert(dec (15,2),reservedpages * 8192 / 1048576.),

convert(dec (15,2),pages * 8192 / 1048576.),

convert(dec (15,2),(usedpages - pages) * 8192 / 1048576.),

convert(dec (15,2),(reservedpages - usedpages) * 8192 / 1048576.)

FROM

(

SELECT sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) AS dbsize

, sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) AS logsize

from dbo.[sysfiles]

) a

LEFT JOIN

(

select sum(a.total_pages) AS reservedpages,

SUM(a.used_pages) AS usedpages,

SUM(

CASE

-- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"

When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0

When a.type <> 1 Then a.used_pages

When p.index_id <2 Then a.data_pages

Else 0

END

) AS pages

from [sys].[partitions] p join [sys].[allocation_units] a on p.partition_id &#61; a.container_id

left join [sys].[internal_tables] it on p.object_id &#61; it.object_id

) b ON 1&#61;1&#39;

select * from #temp1 ORDER BY [数据库大小(MB)] desc

drop table #temp1



推荐阅读
author-avatar
蟹子萝卜控
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有