October 15, 2008 at 9:40 am
HI,
I have been writing a script that produces the syntax to tidly removes a Database, backup history and any logins associated with the DB from the sql instance.
In trying to make it as simple as possible I am trying to paramatise the database name currently it starts like this
declare @Database varchar(50)
/* Please complete the database to be deleted below */
set @Database = 'ajb_test_complete_delete'
use @Database
I obvioulsy get the error Incorrect syntax near '@Database'.
Had a look in BOL and on here and cannot see an example to use or even if it is possible
Thanks
October 15, 2008 at 9:47 am
You cannot use a variable for the database name in a use statement. You would need to use dynamic SQL to accomplish this.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 15, 2008 at 9:49 am
OK, Thanks. Will have a dig in that area then.
October 15, 2008 at 10:58 am
Make use of somehting like:
DECLARE @sql VARCHAR(1000) SELECT @sql = 'exec ' + @servername + '.' +
@databasename+ '.dbo.sp_executesql N''grant select on '+ @tablename + ' to '
+ @username + '''' EXEC (@sql)
Or
DECLARE
@DatabaseName varchar(100), @sql varchar(500)
DECLARE DBNameCursor CURSOR FOR
SELECT top 1
[Name] AS DatabaseName
FROM
master.dbo.sysdatabases
ORDER BY
DatabaseName
OPEN DBNameCursor
FETCH NEXT FROM DBNameCursor
INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
----- Change to Database
SELECT
@sql = 'USE ' + @DatabaseName + char(10)+'select * from sysfiles'
--print @sql
EXEC(@SQL)
FETCH NEXT FROM DBNameCursor
INTO @DatabaseName
END
CLOSE DBNameCursor
DEALLOCATE DBNameCursor
HTH
MJ
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply