|
|
|
Grasshopper
 
Group: General Forum Members
Last Login: Wednesday, May 16, 2012 11:12 PM
Points: 21,
Visits: 244
|
|
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'
|
|
|
|
|
SSChasing Mays
Group: General Forum Members
Last Login: Today @ 1:55 AM
Points: 626,
Visits: 500
|
|
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
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Wednesday, May 16, 2012 11:12 PM
Points: 21,
Visits: 244
|
|
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
|
|
|
|
|
SSChasing Mays
Group: General Forum Members
Last Login: Today @ 1:55 AM
Points: 626,
Visits: 500
|
|
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
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Wednesday, May 16, 2012 11:12 PM
Points: 21,
Visits: 244
|
|
| Thanks Vishal, worked a charm
|
|
|
|
