SQL Server schemas

[skiwi]

I'm after some suggestions on how to model and represent a SQL Server Schema.

In SQL Server a "Schema" is a container with a database to which authorisation can be applied.

At an SQL level

Code: [Select]

Select * from [DBServer].[DBName].[Schema].[Table]
I want to be able to model a database, its schema, and tables.
How would you suggest I represent a schema, and ensure that when the DDL is generated the schema name is present?

thanks in advance

[Paolo F Cantoni]

I think EA places the schema name in the OWNER tagged value.

Consequently, it doesn't really handle it properly.  If you've Reverse Engineered a DB you'll see that ALL the tables and views end up in a single package, regardless of which schema they are in.

The definition of "Schema" across multiple DB technologies is moot...  There's a lovely article on the interweb (whose location I've forgotten) that touches on this.  (Probably Google "Schema")

So, in a sense, Sparx are caught between a rock and a hard place.

Still, like you, I would have expected schema (and it's equivalents) to be a container.

HTH,
Paolo

[«Midnight»]

This becomes a real issue in SQL Server (as only one example) where the concepts of owner and schema became distinct between the 2000 and 2005 versions. Those who design databases that make use of this difference - this could be a common situation in tools that span multiple layers or silos of organizations - will find that EA simply cannot accommodate their needs. Manual intervention becomes necessary. In large models the level of extra effort quickly explodes.

[Paolo F Cantoni]

The article I referred to was:

http://groups.google.com/group/sqlalchemy-devel/browse_thread/thread/4a6551d9182cf5a9

The product is SQLAlchemy, but the discussion is about compatability between RDBMS technologies and syntax.

I remembered that the browser caches the Google Search strings so I found: "mssql schema versus owner"  It's the first result on my list.

HTH,
Paolo