October 26, 2009 at 12:19 pm
Hi,
Im sure there are multiple ways of doing this but I would like to see how this can be done using cursors but am running into problem.This is my 1st time using cursors so not too sure of the way it works. This is just an example that I came up with to practise cursor workings
All I want is for it too display Growth information of all the databases that are available
DECLARE @DbName varchar(30)
DECLARE @DbGrowth varchar(30)
DECLARE TableCursor CURSOR FOR
SELECT [name] from sys.databases
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @DbName
While @@Fetch_Status=0
BEGIN
set @DbGrowth =(select top 1 cast((growth) as varchar(30)) from sys.sysfiles)
Print @DbName
Print @DbGrowth
FETCH NEXT FROM TableCursor INTO @DbName
END
CLOSE TableCursor
DEALLOCATE TableCursor
The results that I end up with is as below.It only returns the results of the Accounting database which is the last in the list for all database.
master
640
tempdb
640
model
640
msdb
640
AdventureWorksDW
640
AdventureWorks
640
Accounting
640
What am I missing here ?
October 26, 2009 at 12:49 pm
Change this:
set @DbGrowth =(select top 1 cast((growth) as varchar(30)) from sys.sysfiles)
to this:
set @DbGrowth =(select top 1 cast((growth) as varchar(30)) from sys.sysfiles where name = @DbName)
Please note, this is untested. You should be able to write this as a set-based query.
October 26, 2009 at 12:52 pm
Let me start by saying, if you are learning cursors to do actual data manipulation or lookups you need to be very careful. The performance is terrible if you are dealing with any decent size datasets. But to answer your question, you aren't changing the database context while looping through the different values of the cursor. This worked for me.
DECLARE @DbName varchar(30)
DECLARE @DbGrowth varchar(30)
DECLARE @sql NVARCHAR(500)
DECLARE @ParmDef NVARCHAR(500)
SET @ParmDef = '@growthOUT varchar(30) output'
DECLARE TableCursor CURSOR FOR
SELECT [name] from sys.databases
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @DbName
While @@Fetch_Status=0
BEGIN
SET @sql = 'use '+@DbName + '; select top 1 @growthOUT = cast((growth) as varchar(30)) from sys.sysfiles'
EXEC sp_executesql @sql, @parmDef, @growthOUT = @DbGrowth OUTPUT
PRINT @dbName
PRINT @DbGrowth
FETCH NEXT FROM TableCursor INTO @DbName
END
CLOSE TableCursor
DEALLOCATE TableCursor
October 26, 2009 at 12:54 pm
Give this code a try:
select
db.name as DBName,
mf.name as DBFileName,
mf.growth as Growth
from
sys.databases db
inner join sys.master_files mf
on (db.database_id = mf.database_id)
October 26, 2009 at 12:56 pm
I knew there was a table I was missing. I couldn't think of the master_files table to save my life.
Thank you Lynn 😀
October 26, 2009 at 12:57 pm
You don't need the dynamic sql in the cursor solution if you change the the table from sys.sysfiles to sys.sysaltfiles.
But I'd look at the code I provided above.
October 26, 2009 at 12:58 pm
Matt Wilhoite (10/26/2009)
I knew there was a table I was missing. I couldn't think of the master_files table to save my life.Thank you Lynn 😀
Well, I can't take all the credit, BOL is my friend. :w00t:
October 26, 2009 at 1:07 pm
Slight change to my original code, might be nice to know if the growth is a precentage or not.
select
db.name as DBName,
mf.name as DBFileName,
mf.growth as Growth,
mf.is_percent_growth as IsPercentGrowth
from
sys.databases db
inner join sys.master_files mf
on (db.database_id = mf.database_id)
October 26, 2009 at 1:15 pm
Thanks All,
Matt : Yes I knew that I had to include 'Use databasename , go , and then the Select statment but was not too sure how to do this ' .Yours worked out just fine
The best solution would be the use of sys.master_files with inclusion of some conditions
to restrict the results
I am glad I learned something today.
October 26, 2009 at 1:51 pm
LOOKUP_BI-756009 (10/26/2009)
Thanks All,Matt : Yes I knew that I had to include 'Use databasename , go , and then the Select statment but was not too sure how to do this ' .Yours worked out just fine
The best solution would be the use of sys.master_files with inclusion of some conditions
to restrict the results
I am glad I learned something today.
The best solution is to do it without using a cursor.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply