Notes

SELECT * FROM RegBuiInd
where CaseNo='UA0085503'
AND BuiType='A'
and UserName in ('29','30','31','32','33','34','35','36','37','38','39','40','41','42','44','08')

EXEC sp_MsForEachTable "Sp_SpaceUsed '?'"




EXEC sp_spaceused 'titles'



SELECT
SCHEMA_NAME(so.schema_id) "Schema",
OBJECT_NAME(ips.[object_id]) "TableName",
ips.index_id "IndexID",
ISNULL(si.name, OBJECT_NAME(ips.[object_id])) "IndexName",
ips.index_type_desc "IndexType",
CAST(sum(ips.page_count)*1.0/128 AS DECIMAL(15, 2)) "Size (MB)",
SUM(ips.page_count) "PageCount",
SUM(ips.record_count) "RowCount"
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
INNER JOIN
sys.indexes AS si
ON
ips.object_id = si.object_id
AND
ips.index_id = si.index_id
INNER JOIN
sys.objects so
ON
si.[object_id] = so.[object_id]
WHERE OBJECT_NAME(ips.[object_id])='SCase'
GROUP BY
so.schema_id,
ips.object_id,
ips.index_id,
si.name,
ips.index_type_desc
ORDER BY
PageCount DESC;






--取得資料表使用
if (select OBJECT_ID('tempdb..#DB_Spece')) IS NOT NULL Drop Table #DB_Spece
Create table #DB_Spece
(Name varchar(200),
Rows int,
Reserved varchar(200),
Data varchar(200),
Index_size varchar(200),
Unused varchar(200))
exec sp_MSforeachtable "insert into #DB_Spece exec sp_spaceused '?'"

--預設以KB顯示，下列以計算為MB
select name,rows,
reserved=(convert(integer,replace(Reserved,' KB',''))/1024),
data=(convert(integer,replace(Data,' KB',''))/1024),
index_size=(convert(integer,replace(Index_size,' KB',''))/1024),
unused=(convert(integer,replace(Unused,' KB',''))/1024)
from #DB_Spece
order by CAST(replace(Data,' KB','') as Integer) desc


--取得資料表使用
--http://blog.csdn.net/zhou5632290/article/details/7262708
set nocount on
declare @db varchar(20)
set @db = db_name()
dbcc updateusage(@db) with no_infomsgs
go
create table #tblspace
(

数据表名称 varchar(50) null,
记录笔数 int null,
保留空间 varchar(15) null,
数据使用空间 varchar(15) null,
索引使用空间 varchar(15) null,
未使用空间 varchar(15) null,

)
declare @tblname varchar(50)
declare curtbls cursor for
select table_name from information_schema.tables
where table_type = 'base table'
open curtbls
Fetch next from curtbls into @tblname
while @@fetch_status = 0
begin
insert #tblspace exec sp_spaceused @tblname
fetch next from curtbls into @tblname
end
close curtbls
deallocate curtbls

select * from #tblspace order by
convert(int,left(保留空间,len(保留空间)-2)) desc
drop table #tblspace