如何获取SQL
Server数据库里表的占用容量大小?
其实只要使用系统内置的存储过程sp_spaceused就可以得到表的相关信息
如:sp_spaceused
'tablename'
step
1:先写一个存储过程,把当前的所有表的相关信息全部都保存在一个指定的表里面
CREATE
PROCEDURE
get_tableinfo
AS
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
@cmdsql
varchar(500)
DECLARE
Info_cursor
CURSOR
FOR
select
o.name
from
dbo.sysobjects
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)
execute
sp_executesql
N'insert
into
tablespaceinfo
exec
sp_spaceused
@tbname',
N'@tbname
varchar(255)',
@tbname
=
@tablename
FETCH
NEXT
FROM
Info_cursor
INTO
@tablename
END
CLOSE
Info_cursor
DEALLOCATE
Info_cursor
GO
step
2:执行存储过程
exec
get_tableinfo
查询运行该存储过程后得到的结果
select
*
from
tablespaceinfo
order
by
cast(left(ltrim(rtrim(reserved))
,
len(ltrim(rtrim(reserved)))-2)
as
int)
desc
分享到:
相关推荐
A DB2 SQL Query - how to get the RACF groups on specified tables
before exploring schema and query optimization, tuning of parameters and how to get the best out of the latest innovations in hardware design. The Guide concludes with recent performance benchmarks ...
How to get current user of sharepoint 2013
It's tough to climb the corporate ladder, but if you want a job that excites you and pays well, you'll likely have to make the climb at some point. If you want to get a promotion, you'll need to be a...
How to get all the IP available in LAN using java
How To Verify the Word Size (32bit vs 64bit) of the Oracle Binary on MS Windows systems
The estimation of total population size for various phenomena of crime is an important factor critical for criminal justice policy formulation and criminological theory development. In this paper, ...
D: common relational patterns A review of some of the most common relational patterns students will encounter in database design such as the Master/Detail relation, weak entities, linking tables, and ...
Conclusion: Both the inconsistency in density and log level and the convergence of context have forced us to question whether it is a reliable means to understand the runtime behavior of software ...
4. One can also identify one’s work with that of the whole field by assuming everyone shares either the idea… 5. The author argues that the substantial development … 6. The paper also presents the ...
How to get SQL Database Size
Each CSS chapter comes with an end-of-chapter exercise where you get to practice the different CSS properties covered in the chapter and see first hand how different CSS values affect the design of ...
Each section describes how to interpret results and express them in a research report after the data are analyzed. For example, students are shown how to phrase the results of a significant and an ...
Suddenly users complains slow response of database. You look at task manager, ORACLE.EXE is taking high CPU almost 100% CPU. As per your knowledge database activity is normal, no new jobs are running....
George Polya revealed how the mathematical method of demonstrating a proof or finding an unknown can be of help in attacking any problem that can be "reasoned" out - from building a bridge to winning...
Step Relational Database DesignTM bridges the gaps between database theory, database modeling, and database implementation by outlining a simple but reliable six-step process for accurately modeling ...
how to organize the tables and what rules to follow to put the right columns into the indexes. So it is abundantly clear that there is a need for a book that goes beyond the usual boundaries and ...
Description: This application demonstrates how to get the window title of an application you started with the shell command. Once you have the Window title you are able to detect the name of the ...
Chapter 16 covers how to use the various forms of replication, while database mirroring is covered in Chapter 17. Classic issues and best practices with backing up and recovering your database are ...
Java howto: Get the hard disk serial number or Motherboard serial number in java.