CODEWITHSUNDEEP
sql-servert-sql
Bomberlt
Bomberlt

Reputation: 259

Create schema in other database

I have scripts running on first database but I want to create schema on other database. Specifically, I'm running this script:

DECLARE @USE_TEMPLATE VARCHAR(MAX) SET @USE_TEMPLATE = 'USE
[{DBNAME}]'

DECLARE @DatabaseName nvarchar(50); DECLARE @HistDatabaseName
nvarchar(50);

SET @DatabaseName = DB_NAME(); SET @HistDatabaseName = @DatabaseName +
N'_Audit'

DECLARE @SQL_SCRIPT VARCHAR(MAX)

SET @SQL_SCRIPT = REPLACE(@USE_TEMPLATE, '{DBNAME}',
@HistDatabaseName) EXECUTE (@SQL_SCRIPT)

DECLARE @schemaTSQL varchar(100) = 'CREATE SCHEMA [newschema]
AUTHORIZATION [dbo];' EXECUTE (@schemaTSQL);

If I hardcode database name it works, but I want this script to be used in EF migrations. For example, this works:

USE [SecondDatabase]

DECLARE @schemaTSQL varchar(100) = 'CREATE SCHEMA [newschema]
AUTHORIZATION [dbo];' EXECUTE (@schemaTSQL);

Upvotes: 1

Views: 2233

Answers (2)

Bukester
Bukester

Reputation: 33

if for just one db that you are not connected to... EXEC('USE tempdb; EXEC(''CREATE SCHEMA test'')')

Upvotes: 1

Fruitbat
Fruitbat

Reputation: 774

I believe this can be done using sp_MSforeachdb.

sp_MSforeachdb is an undocumented stored procedure, so the usual caveats apply. See this article.

Here's a rough idea of how it works:

DECLARE @DatabaseName nvarchar(50); 
DECLARE @HistDatabaseName nvarchar(50);
SET @DatabaseName = DB_NAME(); 
SET @HistDatabaseName = @DatabaseName + N'_Audit'

DECLARE @SQL_SCRIPT NVARCHAR(2000)
DECLARE @schemaTSQL varchar(100) = 'CREATE SCHEMA [newschema] AUTHORIZATION [dbo];' 

SET @SQL_SCRIPT = 'IF ''?'' = ''' + @HistDatabaseName + ''' 
BEGIN
  USE [?]
  IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = ''newschema'')
  EXEC(''' + @schemaTSQL + ''') 
END'

EXEC sp_MSforeachdb @command1=@SQL_SCRIPT

Upvotes: 1

Related Questions