Alter Database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Alter Database

Author

Topic

stumbling
Posting Yak Master

104 Posts

Posted - 2006-07-31 : 03:44:09

Hi all this is my first of most likely many posts.
I am writting a stored proc that will be used on many diffrent named databases and i am setting the database name with a variable. I would like to use this variable in the following situation any clues on this would be greatly appreciated.

DECLARE @DBName varchar(50)
SET @DBName='Database'
alter database @DBName set recovery full

regards
Phil

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-31 : 03:52:59

I dont know whether what you are doing is Adviceable..

you need to built a dynamic SQL for the Same..
Somthing like this


DECLARE @DBName varchar(50)
Declare @QryString Varchar(8000)
SET @DBName='Database' -- or Select @DbName = Db_Name()
Set @QryString = 'alter database ' +  @DBName + ' set recovery full '
Exec(@QryString)

Chirag

stumbling
Posting Yak Master

104 Posts

Posted - 2006-07-31 : 04:37:08

Thanks for the very quick response works a treat
Regards
Phil

Kristen
Test

22859 Posts

Posted - 2006-07-31 : 04:50:52

If you are trying to set all (or most / based on "eye-balling" a list) then my approach would be:

SELECT 'alter database ' + [name] + ' set recovery full'
FROM master.dbo.sysdatabase
ORDER BY [name]

and that will generate a script that you can run (e.g. in Query Analyser)

Kristen

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-31 : 05:01:32


EXEC sp_MSforeachdb @command1='PRINT ''Alter Database ? set recovery full ''', @command2='USE ? SELECT DB_NAME()'

Chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources