Getting dB Size

daniel · December 8th, 2004, 01:12 PM

I know that you can get the size of your databases with exec sp_database. What I want to do is get the size of database 'XYZ' and assign it to a variable. Is this possible?

r_ganesh76 · December 9th, 2004, 05:30 AM

You can use sp_Helpdb

Code:

sp_Helpdb XYZ

Regards
Ganesh

gadhiav · December 9th, 2004, 08:52 AM

You can use sp_Helpdb

sp_Helpdb XYZ

Life is not the Triumph but the Struggle

daniel · December 9th, 2004, 03:52 PM

Thanks for the reply, this does give me the size of the dB, though what I want is something like this

declare @size int

select @size = size
from someSystemTables
where dbName = 'XYZ'

Is this possible?

jemacc · December 9th, 2004, 05:07 PM

Try this

Code:

DECLARE @SQL varchar(8000), @tbname sysname

SELECT @tbname = 'master.dbo.sysfiles'

SELECT @SQL = 'SELECT NAME, SIZE FROM ' + @tbname

EXEC(@SQL)

Jaime E. Maccou

Anantsharma · December 10th, 2004, 06:53 AM

Hi,

Try this

---------------------------------
declare @dbsize numeric ---This is the varibale
create table #tb
(Dbname varchar(100),DBsz numeric,remarks varchar(100))

Insert into #tb exec SP_DATABASES
set @dbsize =(select DBsz from #tb where dbName='YourDbName')
select @dbsize as [Database size]
drop table #tb
---------------------------------

You can pass database name also using a variable.

Hope you got my point.

Gud luck

B. Anant

daniel · December 10th, 2004, 03:31 PM

Fantasic, this is exactly what I want :-)

Thanks very much