InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 03:56:41 GMT20http://www.sqlservercentral.com/Forums/Topic589623-513-1.aspx[quote][b]mr.rajeevgoel (10/22/2008)[/b][hr]hi--select * from tbl_database_detail_excel1 --- this is my table create procedure database_growthasbegindeclare @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_locationinto #backupinfofrom tbl_database_detail_excel1 */select database_name,server_name, database_size, datafie_size, logfile_size, datafile_location, logfile_location from tbl_database_detail_excel1open c1FETCH c1 into @dbnameWHILE @@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 @dbend close c1endwhen i exceute this sp, the error shows "Invalid object name 'database_growth' ". so please do something for me to resolve this problem.[/quote]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 ) ) aorder 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/needWed, 22 Oct 2008 01:33:03 GMTWandraghttp://www.sqlservercentral.com/Forums/Topic589623-513-1.aspxhi--select * from tbl_database_detail_excel1 --- this is my table create procedure database_growthasbegindeclare @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_locationinto #backupinfofrom tbl_database_detail_excel1 */select database_name,server_name, database_size, datafie_size, logfile_size, datafile_location, logfile_location from tbl_database_detail_excel1open c1FETCH c1 into @dbnameWHILE @@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 @dbend close c1end when i exceute this sp, the error shows "Invalid object name 'database_growth' ". so please do something for me to resolve this problem.Wed, 22 Oct 2008 01:19:05 GMTmr.rajeevgoel