Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

growth db Expand / Collapse
Author
Message
Posted Wednesday, October 22, 2008 1:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 22, 2008 8:55 AM
Points: 1, Visits: 8
hi

--select * from tbl_database_detail_excel1 --- this is my table

create procedure database_growth
as
begin

declare @dbname nvarchar(1000)
declare @db nvarchar(100)

declare c1 cursor for


/*select database_name,server_name, database_size, datafie_size, logfile_size, datafile_location, logfile_location
into #backupinfo
from tbl_database_detail_excel1 */

select database_name,server_name, database_size, datafie_size, logfile_size, datafile_location, logfile_location from tbl_database_detail_excel1
open c1



FETCH c1 into @dbname
WHILE @@FETCH_STATUS >= 0
BEGIN


select @db = 'select ' + '''' + @db + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from [' + @db + '].dbo.sysfiles'


exec (@db)
fetch c1 into @db
end
close c1
end



when i exceute this sp, the error shows "Invalid object name 'database_growth' ". so please do something for me to resolve this problem.
Post #589623
Posted Wednesday, October 22, 2008 1:33 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 14, 2014 6:53 AM
Points: 550, Visits: 1,061
mr.rajeevgoel (10/22/2008)
hi

--select * from tbl_database_detail_excel1 --- this is my table

create procedure database_growth
as
begin

declare @dbname nvarchar(1000)
declare @db nvarchar(100)

declare c1 cursor for


/*select database_name,server_name, database_size, datafie_size, logfile_size, datafile_location, logfile_location
into #backupinfo
from tbl_database_detail_excel1 */

select database_name,server_name, database_size, datafie_size, logfile_size, datafile_location, logfile_location from tbl_database_detail_excel1
open c1



FETCH c1 into @dbname
WHILE @@FETCH_STATUS >= 0
BEGIN


select @db = 'select ' + '''' + @db + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from [' + @db + '].dbo.sysfiles'


exec (@db)
fetch c1 into @db
end
close c1
end



when i exceute this sp, the error shows "Invalid object name 'database_growth' ". so please do something for me to resolve this problem.


Why do you use a cursor?
Why not just a straight select - you don't need a cursor here...

use something like this:
declare @SQL varchar(max)
set @SQL = '
use [?] ;

select top 100 percent
[DATABASE_NAME] = db_name(),
[FILEGROUP_TYPE] = case when a.groupid = 0 then ''Log'' else ''Data'' end,
[FILEGROUP_ID] = a.groupid,
a.[FILEGROUP],
[FILEID] = a.fileid,
[FILENAME] = a.name,
[DISK] = upper(substring(a.filename,1,1)),
[FILEPATH] = a.filename,
[MAX_FILE_SIZE] = a.maxsize ,--case a.maxsize when -1 then ''UNLIMITED'' else (a.maxsize*1.000)/128.000 end,
[FILE_SIZE] = a.[fl_size],
[FILE_SIZE_USED] = a.[fl_used],
[FILE_SIZE_UNUSED] = a.[fl_unused],
cast(case when (status & 0x100000) > 0 and [growth] > 0 then cast([growth] as varchar(10)) + '' %'' else cast(([growth]/128) as varchar(10)) + '' MB'' end as varchar(10))
from
(
Select
aa.*,
[FILEGROUP] = isnull(bb.groupname,''''),
-- All sizes are calculated in MB
[fl_size] =
convert(int,round((aa.size*1.000)/128.000,0)),
[fl_used] =
convert(int,round(fileproperty(aa.name,''SpaceUsed'')/128.000,0)),
[fl_unused] =
convert(int,round((aa.size-fileproperty(aa.name,''SpaceUsed''))/128.000,0))
from
dbo.sysfiles aa
left join
dbo.sysfilegroups bb
on ( aa.groupid = bb.groupid )
) a
order by
case when a.groupid = 0 then 0 else 1 end,
a.[FILEGROUP],
a.name
'
exec sp_msforeachdb @sql



-- Just remove/modify the fields you don't want/need
Post #589629
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse