SQL SERVER: Issues with TSQL to check if schema on database exists

Question

On SQL SERVER:

I have a schema on my database created using command:

CREATE SCHEMA my-schema AUTHORIZATION [G_USER]

When I try search for the schema using the following statement I do not get any result.

SELECT * FROM sys.schemas WHERE name=N'my-schema'

However when I try SELECT * FROM sys.schemas I get a whole bunch of other schemas but not the my-schema which I created on the database.

Am I doing something wrong? Any Help will be greatly appreciated.
Thanks!

http://stackoverflow.com/questions/1317398/get-sql-server-schema-via-a-sql-query — Yosi Dahari, Sep 17 '13 at 14:31
Are you sure you created it? The statement should fail with a syntax error unless you use brackets around `my-schema`. — GSerg, Sep 17 '13 at 14:31
Is that the exact query you were using to create the schema?, if not, please show us the right one. — Lamak, Sep 17 '13 at 14:35
the schema can be seen under my-database/security/schemas/my-schema/ when I click down in MSSMS — aruuuuu, Sep 17 '13 at 14:42
`sys.schemas` lists schemas from current database. Try `use [my-database]; select * from sys.schemas;`. — GSerg, Sep 17 '13 at 14:50
How about select * from [my-database].sys.schemas WHERE name='my-schema'; ? — aruuuuu, Sep 17 '13 at 15:37

score 2 · Accepted Answer · answered Sep 17 '13 at 14:34

Here is my syntax (from the personal code-snipplets vault)

if not exists(select 1 from information_schema.schemata where schema_name='my-schema')
BEGIN
    print 'Creating the schema : [my-schema]' 
    EXEC ('CREATE SCHEMA [my-schema] AUTHORIZATION dbo;')
END


if exists(select 1 from information_schema.schemata where schema_name='my-schema')
BEGIN
print 'Schema Exists : [my-schema]' 
END

(My personal code below, just in case)

if not exists(select 1 from information_schema.schemata where schema_name='LookupSchema')
BEGIN
    EXEC ('CREATE SCHEMA LookupSchema AUTHORIZATION dbo;')
END

GO

SQL SERVER: Issues with TSQL to check if schema on database exists

1 Answers1