August 9, 2011 at 3:32 pm
Hai,
all Canyou plz tell me
i need to fetch these details database name & tablename & table size in MB
thanks you So much ............
:hehe:
Rajesh
August 9, 2011 at 3:33 pm
Very detailed version...
August 9, 2011 at 3:38 pm
hey
Can you plz give me brief info I know SP_SPACEUSED stored procedure it will give
i need only database Name & table name & size Yar
NameRowsreserveddataindex_sizeUnused
August 9, 2011 at 3:45 pm
what extra info do you need on spaceused???
For all dbs, all tables this is something that would work, but I would definitely use Jason's script. It has a lot of very usefull extra information in it.
SET STATISTICS IO, TIME OFF
SET NOCOUNT ON
SET IMPLICIT_TRANSACTIONS ON
GO
USE [master]
GO
IF OBJECT_ID('dbo.spaceused', 'U') IS NULL
BEGIN
CREATE TABLE dbo.spaceused (
DbName sysname DEFAULT(''),
tblName sysname,
Row_count INT ,
Reserved VARCHAR(50),
data VARCHAR(50) ,
index_size VARCHAR(50),
unused VARCHAR(50),
PRIMARY KEY CLUSTERED (DbName, tblName)
);
END
ELSE
BEGIN
--DROP TABLE dbo.spaceused
TRUNCATE TABLE dbo.spaceused
END
COMMIT
GO
DECLARE @Cmd VARCHAR(8000)
SET @Cmd = 'USE [?];
IF ''?'' NOT IN (''tempdb''
--, ''master'', ''model'', ''msdb''
)
BEGIN
--PRINT ''?''
DECLARE @InnerCmd VARCHAR(8000)
SET @InnerCmd = ''
EXEC sp_spaceused '''''' + CHAR(63) + ''''''''
INSERT INTO master.dbo.spaceused(tblName, Row_count,Reserved,data,index_size,unused)
EXEC sp_MSforeachtable @InnerCmd
UPDATE master.dbo.spaceused SET DbName = ''?'' WHERE DbName = ''''
END
'
--PRINT @Cmd
EXEC sp_MSforeachdb @Cmd
DELETE FROM dbo.spaceused WHERE Row_count = 0
SELECT
DbName
, tblName
, Row_count
, CONVERT(BIGINT, REPLACE(Reserved, ' KB', '')) / 1024 AS MB_Reserved
, CONVERT(BIGINT, REPLACE(data, ' KB', '')) / 1024 AS MB_data
, CONVERT(BIGINT, REPLACE(index_size, ' KB', '')) / 1024 AS MB_index_size
, CONVERT(BIGINT, REPLACE(unused, ' KB', '')) / 1024 AS MB_unused
FROM
dbo.spaceused
ORDER BY
DbName
, MB_Reserved DESC
, Row_count DESC
COMMIT
August 9, 2011 at 3:51 pm
Thank you NINJA;-)
August 9, 2011 at 4:16 pm
Be carfull of using sp_spaceused. If your statistics are out of date, the table sizes may not be correct.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 9, 2011 at 4:19 pm
Another reason to use => 😉
Ninja's_RGR'us (8/9/2011)
Very detailed version...
August 9, 2011 at 4:58 pm
Leo.Miller (8/9/2011)
Be carfull of using sp_spaceused. If your statistics are out of date, the table sizes may not be correct.
Statistics do not in any way effect the output of sp_spaceused. Statistics are just aggregated information on the value distribution in columns of tables. Nothing space-related at all.
What used to cause problems with sp_spaceused was inaccurate page usage information (there were bugs in the space-tracking algorithms on SQL 2000) resulting in incorrect flags for page usage on the PFS pages. This can be fixed with DBCC UPDATEUSAGE (not UPDATE STATISTICS)
The bugs in the space-tracking algorithms were supposed to be fixed in 2005 RTM, but some persisted until SP3.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 9, 2011 at 5:00 pm
Thans for the edit Gail... was starting to have seconds about that one myself... but I'm a little busy on another fun thread :-D.
So of the solution provided so far which one would you preffer?
August 9, 2011 at 5:08 pm
errr It Depends (on what I'm trying to do)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply