April 25, 2005 at 6:27 am
April 25, 2005 at 6:34 am
A quick and dirty way moght look like
USE NORTHWIND
GO
SELECT TOP 1
used AS "# of Pages"
, rows AS "# of Rows"
, (used * 8) / 1024 AS "# of MB"
, CAST(OBJECT_NAME(id) AS CHAR(30)) AS TableName
FROM
sysindexes
--WHERE
-- indid =1
ORDER BY
used
DESC
# of Pages # of Rows # of MB TableName
----------- ----------- ----------- ------------------------------
59 830 0 Orders
(1 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 25, 2005 at 8:01 am
Here is a little different twist on your request.
-- Create the temp table for further querying
CREATE TABLE #temp(
ServerName varchar(30),
DBName varchar(40),
rec_id int IDENTITY (1, 1),
table_name varchar(128),
nbr_of_rows int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
percent_of_db decimal(15,12),
db_size decimal(15,2))
-- Get all tables, names, and sizes
EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",
@command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"
-- Set the total_size and total database size fields
UPDATE #temp
SET total_size = (data_space + index_space),
db_size = (SELECT SUM(data_space + index_space) FROM #temp),
ServerName = @@ServerName,
DBName = DB_Name()
-- Set the percent of the total database size
UPDATE #temp
SET percent_of_db = (total_size/db_size) * 100
-- Get the data
-- Modify the percent_of_db int the where clause as
-- desired
SELECT
ServerName,
DBName,
table_name,
nbr_of_rows,
data_space,
index_space,
total_size,
cast(percent_of_db as decimal(5,2)) as PCNT,
db_size
FROM #temp
where percent_of_db > .99
ORDER BY total_size DESC
-- Comment out the following line if you want to do further querying
DROP TABLE #temp
April 26, 2005 at 8:39 am
Here is another solution, that list all tables and database size.
CREATE TABLE #temp(
rec_id int IDENTITY (1, 1),
table_name varchar(128),
nbr_of_rows int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
percent_of_db decimal(15,12),
db_size decimal(15,2))
-- Get all tables, names, and sizes
EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",
@command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"
-- Set the total_size and total database size fields
UPDATE #temp
SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)
-- Set the percent of the total database size
UPDATE #temp
SET percent_of_db = (total_size/db_size) * 100
-- Get the data
SELECT *
FROM #temp
ORDER BY total_size DESC
-- Comment out the following line if you want to do further querying
DROP TABLE #temp
Azzam.
April 26, 2005 at 4:56 pm
Here's my script:
select
so.id as [OBJECT_ID],
so.name as [OBJECT_NAME],
coalesce(j_rows.rows,0) as [ROWCOUNT],
coalesce(j_ru.sum_reserved,0) * cast(m.low as dec) / 1024 as [RESERVED (KB)],
d.data * cast(m.low as dec) / 1024 as [DATA (KB)],
(coalesce(j_ru.sum_used,0) - d.data) * cast(m.low as dec) / 1024 as [INDEX (KB)],
(coalesce(j_ru.sum_reserved,0) - coalesce(j_ru.sum_used,0)) * cast(m.low as dec) / 1024 as [UNUSED (KB)]
from
sysobjects so
-- rows
left join sysindexes j_rows
on j_rows.indid < 2 and j_rows.id = so.id
/* reserved: sum(reserved) where indid in (0, 1, 255) */
/* index: sum(used) where indid in (0, 1, 255) - data */
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
left join
(
select
id, sum(reserved) as sum_reserved, sum(used) as sum_used
from
sysindexes
where
indid in (0, 1, 255)
group by
id
  j_ru on j_ru.id = so.id
/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
left join
(
select
j_dpages.id, coalesce(j_dpages._sum,0) + coalesce(j_used._sum,0) as data
from
(
select
id, sum(dpages) as _sum
from
sysindexes
where
indid < 2
group by
id
  j_dpages left join
(
select
id, sum(used) as _sum
from
sysindexes
where
indid = 255
group by
id
  j_used on j_used.id = j_dpages.id
  d on d.id = so.id
inner join master.dbo.spt_values m
on m.number = 1 and m.type = 'E'
where
OBJECTPROPERTY(so.id, N'IsUserTable') = 1
order by
[DATA (KB)] DESC, [ROWCOUNT] ASC
April 26, 2005 at 4:58 pm
not sure why the ')' all show up as smileys.
April 27, 2005 at 12:30 am
Thats the forum software. It parses the text for characters to convert to smilies. Pretty annoying when posting code
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 28, 2005 at 2:21 am
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply