0

I know you can do this

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'log')
BEGIN
    -- some code 
END

But how to check if a schema exists in a specific database? The database name is available as @dbname.

First attempt

An easy fix is 

exec ('use db_name; select schema_id(''log'')')

But if you want to use that in an ifconstruction you end up with some ugly sp_executesql syntax. So what is the best way to check if a schema exists in a specific database?

Roelant
  • 4,508
  • 1
  • 32
  • 62

1 Answers1

3

I would simply do:

IF NOT EXISTS (SELECT * FROM DBName.sys.schemas WHERE name = 'log')
BEGIN
    -- some code 
END

Just tried it in my environment and it works.

Esteban P.
  • 2,789
  • 2
  • 27
  • 43
  • Thanks! Not sure why I missed something so obvious. – Roelant Mar 27 '19 at 09:57
  • Actually, didn't explain that part of the problem is that I only have access to the `@dbname` (a parameter). Was hoping there would be some master table so I can use a `where` instead of `sp_executesql` – Roelant Mar 27 '19 at 12:15
  • @Roelant: in general, database-scoped objects can only be accessed from within the DB, either through `use` or by explicitly prefixing them (the only real exceptions are system table-valued functions that accept DB names/IDs as parameters), meaning you can't get around dynamic SQL. This is an unfortunate and unavoidable pain when you're dealing with dynamic names, but the alternative would be views that span databases with some very ugly cross-database locking. – Jeroen Mostert Mar 27 '19 at 12:29