August 16, 2009 at 6:48 pm
Hi All
I would like to know Datbase Create Datae, Size for one of my project. I find some info from google but now I am stuck as Size is only working for current datbase but I need to know Size of databse what I am passing in parameters. See the query below and tell me how can i get size for every differnet database.
Note: I am calling this store procedure from .C# Code.
USE [xxx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: xx-- Create date:
-- Description: Get Database Create Datae,Physical File Location and Size
-- =============================================
CREATE PROCEDURE [dbo].[db_sp_test]
-- Add the parameters for the stored procedure here
@databaseName nvarchar(1000)
AS
BEGIN
declare @fileNameFromDatabase nvarchar(1000)
,@query nvarchar(1000)
,@pages bigint
,@queryFORSIZE varchar(200)
select @pages = sum(
CASE
When it.internal_type IN (202,204) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
--select data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB')
select crdate,filename,(select ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB')) as size from master..sysdatabases
where name =@databaseName
END
August 16, 2009 at 10:47 pm
oo sure, but is this what you are looking for?
select
db.[name] as DatabaseName,
db.[create_date] as CreateDate,
mf.[name] as LogicalName,
mf.[physical_name] as PhysicalName,
cast(cast((mf. * 8192.)/1024 as decimal(16,2)) as varchar(16)) + ' KB' as DatabaseSize
from
sys.databases db
inner join sys.master_files mf
on (db.database_id = mf.database_id);
August 16, 2009 at 11:06 pm
Hi Lynn
The value of size is not correct if you compare with sp_spaceused data column .
If you run sp_spaceused it will give you two result sets and I want to print data( column Name)Value from second result set.
Thanks
Lincs
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply