July 2, 2009 at 4:49 am
Hi Guys
Unable to Insert Dynamic Database name into new table here is my query
This is the cursor iam using for
Declare dbname_curr cursor for
select [name] from sysdatabases where [name] not in ('tempdb')
order by [name]
open dbname_curr
fetch next from dbname_curr into @dbname
while @@fetch_status <> -1
begin
SELECT @command = 'Use ' + @dbname + ' Insert into master.dbo.DBInfo SELECT
@@servername as ServerName,
'+ @dbname +' as Databasename,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName,
filename AS PhysicalFileName,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +
'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,
CASE WHEN maxsize = 0 THEN LTRIM(STR(growth * 8.0 / 1024,10,1)) + '' MB,''
ELSE ''By '' + CAST(growth AS VARCHAR) + '' percent, '' END +
CASE WHEN maxsize = -1 THEN ''Unrestricted Growth''
ELSE ''restricted growth to '' + LTRIM(STR(maxsize * 8.0 / 1024,10,1)) + '' MB''
END AS Autogrow,
GETDATE() as PollDate from dbo.sysfiles'
Print @command
Exec (@command)
fetch next from dbname_curr into @dbname
end
close dbname_curr
deallocate dbname_curr
And As a Output Iam getting
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'QuestSoftware'.
Use QuestSoftware Insert into master.dbo.DBInfo SELECT
@@servername as ServerName,
QuestSoftware as Databasename, ** Iam getting without ' ' ***
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName,
filename AS PhysicalFileName,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, 'SpaceUsed' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,
'SpaceUsed' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + '%' AS FreeSpacePct,
CASE WHEN maxsize = 0 THEN LTRIM(STR(growth * 8.0 / 1024,10,1)) + ' MB,'
ELSE 'By ' + CAST(growth AS VARCHAR) + ' percent, ' END +
CASE WHEN maxsize = -1 THEN 'Unrestricted Growth'
ELSE 'restricted growth to ' + LTRIM(STR(maxsize * 8.0 / 1024,10,1)) + ' MB'
END AS Autogrow,
GETDATE() as PollDate from dbo.sysfiles
Can some one Please help me out on this.
Regards
Nag
July 2, 2009 at 5:35 am
Hello,
Could you use sp_msforeachdb instead of the cursor and Use statement?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
July 2, 2009 at 7:31 am
Nag (7/2/2009)
'+ @dbname +' as Databasename,
Nag, (cute tag...)
change this to:
'+ QuoteName(@dbname, char(39)) +' as Databasename,
That should resolve your issue.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 2, 2009 at 7:48 pm
Thanks Wayne 🙂 Its worked
Regards
Nag
July 2, 2009 at 7:51 pm
Hello John
Iam using this script in production servrs, I saw some were this "sp_msforeachdb" undocumented and which is not recommeded by microsoft
to use it on Production. and also few of my SQL Server 2000 servers don't have this SP by default.
Regards
Nag
July 2, 2009 at 9:39 pm
Nag,
Yes, sp_msForEachDB is an undocumented system stored procedure. Which just means that google will have the information you need for how to use it.
What I find ironic is the suggestion to replace your cursor with this... if you look at the code for it, it is a cursor.
There is nothing wrong with keeping this cursor - it looks like it is for an automated admin task.
FYI, I've never seen a server that didn't have sp_msForEachDB in the master db. The only way I can picture this happening is if it were to be deliberately dropped.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 2, 2009 at 11:01 pm
Hello Wayne,
You can find a definition of irony here: http://www.askoxford.com/concise_oed/irony?view=uk
You may like to spend your time writing code to duplicate logic already provided elsewhere but that does not mean everyone does.
Have a nice day,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
July 3, 2009 at 3:22 am
Hello Nag,
If you want to consider using sp_msforeachdb, then I have tested the code below under SQL 2005. It should work Okay for 2000.
Regards,
John Marsh
PS: During my (limited) testing, I did notice that using the int data type for File Size related fields caused some significant rounding. You might want to consider using another numeric data type.
Declare
@DBInfo Table
(ServerName sysname,
Databasename sysname,
FileSize int,
LogicalFileName sysname,
PhysicalFileName nvarchar(260),
FreeSpaceMB int,
FreeSpacePct nvarchar(8),
Autogrow nvarchar(50),
PollDate datetime)
Insert Into @DBInfo
Exec sp_MSforeachdb
'Select
@@servername as ServerName,
''?'' as Databasename,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName,
filename AS PhysicalFileName,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ''SpaceUsed'') AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name, ''SpaceUsed'') AS int)/128.0)/(sysfiles.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ''%'' AS FreeSpacePct,
CASE WHEN maxsize = 0 THEN LTRIM(STR(growth * 8.0 / 1024,10,1)) + '' MB,''
ELSE ''By '' + CAST(growth AS VARCHAR) + '' percent, '' END +
CASE WHEN maxsize = -1 THEN ''Unrestricted Growth''
ELSE ''restricted growth to '' + LTRIM(STR(maxsize * 8.0 / 1024,10,1)) + '' MB''
END AS Autogrow,
GETDATE() as PollDate from dbo.sysfiles'
Select * From @DBInfo
www.sql.lu
SQL Server Luxembourg User Group
July 3, 2009 at 5:59 am
John Marsh (7/2/2009)
Hello Wayne,You can find a definition of irony here: http://www.askoxford.com/concise_oed/irony?view=uk
You may like to spend your time writing code to duplicate logic already provided elsewhere but that does not mean everyone does.
Have a nice day,
John Marsh
His point was that he was cautious about using an undocumented stored procedure. While I personally don't see any reason not to use it, I understand his position.
If he doesn't want to use an undocumented stored procedure, then his only recourse is to duplicate the functionality in his code. And since he has already duplicated it, he should just go ahead and keep it.
Note that just moving his code from his cursor to using sp_MSForEachDB would not have corrected his problem.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 4, 2009 at 2:24 am
Yes, I agree with you Wayne
Thanks
Nag
July 4, 2009 at 2:31 am
Hi John
Thanks for your script john I have tried my script with "sp_MSforeachdb" SP which worked fine .
Actually I don't want to use this SP So iam using cursor.
Regards
NAG
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply