SQL Server 2005/2008 Schemas

[JoeCool]

Hi, I was wondering if it was possible to assign tables in a EA diagram to different SQL Server schemas, so that when I generate DDL it will include the schema assignments. I am not talking about schemas in the usual modeling since (where the default one is "Schema1"), but in the organizational containers that SQL Server uses.

Thanks,
-Robert

[«Midnight»]

If you mean schema in the sense of a USE clause, you are somewhat stuck. EA stores this information at the application level (i.e. across all EA sessions and models) in the registry. Thus changing this value affects every DDL file generated from that point on.

I mentioned this to Sparx (in the context of a bug report or feature request, depending on viewpoint) some time ago but received no response.

You can qualify tables with an Owner, which might serve your purpose. This is a legacy feature though, so the finer-grained model from MS SQL 2005 and later might not be fully supported (I just don't know). You need to do two things for this to work. First, set the table owner using a tagged value. In the EA help index look up Table | Set Owner to see how this is done. Then when you generate DDL check the Generate Table Owner box (this appears on both varieties of Generate DDL dialog, just above the Use Database text box).

HTH, David

[JoeCool]

USE is for changing databases from what I can tell. I'm talking about the container objects that have been around since the 2005 version. Before 2005, you could only assign tables to owners (like you mention), which was a pain. Now you can make schemas and assign tables to them to give your database a bit more organization. It's very much like the schemas in EA, but I don't think they're the same thing. Either way, I'd like to model SQL Server schemas in EA, but I don't thing it's possible...

[JoeCool]

David, I've discovered your method works for schemas in 2005/2008 just like it did for owners before.

The only issue now is I'd like to be able to display the schema (still referred to as owner in EA) on the table name in the diagram - I have some tables named the same thing that are in two different schemas.

Anyway, thanks for the help!

[«Midnight»]

I think there's an option somewhere to display table owners. But I am not sure where, or even whether I remember correctly. Look around the various options dialogs.