Dynamic SQL in sp_MSforeachdb
-
Hello all,
I have some TSQL that i want to use with SP_MSFOREACHDB , but i seem to be having problems with the single quotes.
select
'ALTER DATABASE '+a.name+' MODIFY FILE ( NAME = N'''+b.physical_name+''', MAXSIZE = UNLIMITED)'
from sys.database_files a, sys.database_files b where a.type =0 and b.type=1
WORKS
EXEC sp_MSforeachdb 'USE ?
ALTER DATABASE +a.name+ MODIFY FILE ( NAME = N+b.physical_name+, MAXSIZE = UNLIMITED)
from sys.database_files a, sys.database_files b where a.type =0 and b.type=1'
doesn't 🙁
My eyes would appreciate your help 🙂
-
I did part of this - I'm certain that you can work out the rest.
exec sp_MSforeachdb @command1 = 'use ?; select ''ALTER DATABASE ? MODIFY FILE ( NAME = N''+ b.physical_name + '', MAXSIZE = UNLIMITED)'' from sys.database_files b'The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thanks Phil , those pesky quotes were swilling all over the place 🙂
-
exec sp_MSforeachdb @command1 = 'use ?; select ''ALTER DATABASE ? MODIFY FILE ( NAME = N''''''+ b.physical_name +'''''', MAXSIZE = UNLIMITED)'' as "code" from sys.database_files b'
lots of '
-
Can I ask what you are trying to achieve?
You script is generating an ALTER DATABASE statement using the name of your data file as the database name and using the physical name (path) of the log file as the file name. Can I assume you are trying to set unlimited file growth on all your log files? If my assumption is correct then this will work:
SELECT'ALTER DATABASE ' + DB_NAME(database_id) + ' MODIFY FILE (NAME = N''' + name + ''', MAXSIZE = UNLIMITED)'
FROM
sys.master_files
WHERE
type = 1
-
Hey Sean , you you are right , but it was really yo do with the '
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply