CODEWITHSUNDEEP
sql-serverdatabase-schema
Roelant
Roelant

Reputation: 5119

Check if schema exists in a specific database on sql server

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?

Upvotes: 1

Views: 7150

Answers (1)

Esteban P.
Esteban P.

Reputation: 2809

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.

Upvotes: 5

Related Questions