November 2, 2011 at 10:28 pm
Hello
I'm trying to drop any custom schemas that exist in a certain set of databases determined by the variable value passed. I have written the code below but I get errors. Does anybody know of a way of passing a variable to the proc sp_MSForeachdb or of an alternative way to do this. If I try to do this using cursors I run into errors as I cannot drop a schema using a fully qualified name, I have to write a 'use database' command to change the database then drop the schema
declare @db_type as varchar(10)
set @db_type = '%_lts%'
EXEC master..sp_MSForeachdb '
USE [?]
IF ''?'' like '@db_type'
BEGIN
declare @sql as varchar(1000)
select @sql = ''drop schema '' + name from sys.schemas
where name not in(''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys''
,''db_owner'',''db_accessadmin'',''db_securityadmin'',''db_ddladmin'',''db_backupoperator''
,''db_datareader'',''db_datawriter'',''db_denydatareader'',''db_denydatawriter'')
exec (@sql)
END'
November 2, 2011 at 11:39 pm
EXEC master..sp_MSForeachdb '
declare @db_type as varchar(10)
set @db_type = ''%_lts%''
USE [?]
IF ''?'' like '@db_type'
BEGIN
declare @sql as varchar(1000)
select @sql = ''drop schema '' + name from sys.schemas
where name not in(''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys''
,''db_owner'',''db_accessadmin'',''db_securityadmin'',''db_ddladmin'',''db_backupoperator''
,''db_datareader'',''db_datawriter'',''db_denydatareader'',''db_denydatawriter'')
exec (@sql)
END'
Vishal Gajjar
http://SqlAndMe.com
November 6, 2011 at 8:21 pm
Thanks for the reply Vishal,
I'm actually getting an error when I run the SQL:
/*------------------------
EXEC master..sp_MSForeachdb '
declare @db_type as varchar(10)
set @db_type = ''%_lts%''
USE [?]
IF ''?'' like '@db_type'
BEGIN
declare @sql as varchar(1000)
select @sql = ''drop schema '' + name from sys.schemas
where name not in(''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys''
,''db_owner'',''db_accessadmin'',''db_securityadmin'',''db_ddladmin'',''db_backupoperator''
,''db_datareader'',''db_datawriter'',''db_denydatareader'',''db_denydatawriter'')
exec (@sql)
END'
------------------------*/
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '@db_type'.
Also I want to use this in a stored procedure so one of the variables will be @db_type, therefore I will need to pass this variable into the dynamic SQL
November 6, 2011 at 9:34 pm
Try this:
DECLARE @sqlCommand VARCHAR(2000)
declare @db_type as varchar(10)
set @db_type = '%_lts%'
SET @sqlCommand =
'
USE [?]
PRINT ''?''
IF ''?'' like ''' + @db_type
+
'''
BEGIN
DECLARE @sql VARCHAR(1000)
SELECT @sql = ''DROP SCHEMA '' + name FROM sys.schemas
WHERE name NOT IN (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys''
,''db_owner'',''db_accessadmin'',''db_securityadmin'',''db_ddladmin'',''db_backupoperator''
,''db_datareader'',''db_datawriter'',''db_denydatareader'',''db_denydatawriter'')
PRINT (@sql)
END'
EXEC master..sp_MSForeachdb @sqlCommand
PS: I have replace EXEC with PRINT for testing...
Vishal Gajjar
http://SqlAndMe.com
November 6, 2011 at 10:42 pm
Thanks Vishal, worked a charm
September 4, 2014 at 12:29 pm
Vishal, Are you still there? I have seen more examples of sp_MSforeachdb than I care to see, but I have yet to see anything that does what I want. It seems to be a simple (and probably common) need. I would like to do an EXEC on each database, executing a stored procedure contained in one database only. I also need to be able to pass a parameter to it. I have spent many hours without success. Here is what I am trying to do, but cannot seem to accomplish:
SET @SQL = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN USE ? EXEC Utility.dbo.desiredprocedure @Parameter END'
EXEC sp_MSforeachdb @SQL
What am I missing?
September 4, 2014 at 12:37 pm
Vishal, I forgot to add one additional note. The preceding does seem to be executed on every DB (except the four that are excluded), but for each of the executions (eleven for this particular SQL Server instance), it seems to be running on the [Utility] database, rather than the eleven databases used by the sp_MSforeachdb.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply