Call for Gurus!. converting msSQl schema into standard format (DDL, XML)

Hi,

I am wondering if any of the DB Gurus out there help me finding a way to export mssql SCHEMA (not the actual data) into any standard language, like DDL, XML. At this point converting to XML would be the ideal but I am open to DDL format also.

I would love to hear any feedback about "If this is even possible
or not ?".

thanks,
tinkiSchema can be in a SQL script form. So, ideally this tool that
I am looking for will convery this SQL script info to DDL or
XML.

Thanks in advance,
Tinki|||Have you tried Enterprise Manager?|||DDL, or Data Definition Language, is RDBMS-specific, meaning that it is not like COBOL, VBScript, or XML. DDL for Oracle will look differently than DDL for Sybase for the same set of tables, while certain objects in Oracle will not even be found in Sybase. XML on the other hand is a standard.

Try DBArtisan from Embarcadero. Even though it only has an option "Extract Data as XML", it also allows you to generate the schema portion of your data.|||mySQL and SQL Server have very similar DDLs|||I'm glad|||Originally posted by Paul Young
Have you tried Enterprise Manager?

yes, I have tried Enterprise manager. It lets me export data to SQL script (which is very database specific). what I am looking at something
which can convert this SQL script to something more wodely accepted as a general standard so that my rest of the code does not get too
much database dependent.

tinki|||Originally posted by rdjabarov
DDL, or Data Definition Language, is RDBMS-specific, meaning that it is not like COBOL, VBScript, or XML. DDL for Oracle will look differently than DDL for Sybase for the same set of tables, while certain objects in Oracle will not even be found in Sybase. XML on the other hand is a standard.

Try DBArtisan from Embarcadero. Even though it only has an option "Extract Data as XML", it also allows you to generate the schema portion of your data.

thanks rdjabarov, I will try DBArtisan.

I have tried XMLSPY but it couldn't get to export schema. It was exporting only the data.

tinki.|||Originally posted by rdjabarov
I'm glad

I'm glad your glad!

Should have read tinki's post a little closer, thought he wanted a MSSQL to MYSQL convertion.|||DBArtisan is somewhat pricy, but a must-have tool. Also does lots of other things.|||Originally posted by tinki
what I am looking at something which can convert this SQL script to something more wodely accepted as a general standard so that my rest of the code does not get too much database dependent.
tinki

Are you actually looking for a db-independent db structure, rather than exporting your current db structure? Then, you should take these recommendations into account:

1) use short (table & field) identifiers (Informix 7, for instance, has a maximum length of 18, and Oracle 40)
2) use just characters, digits and the underscore for your identifiers
3) use just ANSI SQL data types, and not, for example, BOOLEAN (BIT)
...
I'm sure that the real gurus in this forum can easely enhance this list.

However, using a data model with no db-specific properties makes it easy to build a db-independent application, with or without a middleware encapsulating db-specific properties.

Calendar dimension with multiple Fiscal calendars

I am building a data warehouse using the dimensional model -- i.e. fact and
dimensional tables in a star schema.
For my Calendar dimension, I want to include Fiscal periods, but the data
warehouse has data for different clients that have different fiscal calendars.
How do I handle that ?
If I understand well, you have:
Customer A - 15/10/2006 - CY 2006 FY 2007
Customer A - 30/09/2006 - CY 2006 FY 2006
Customer B - 15/10/2006 - CY 2006 FY 2006
Customer B - 15/10/2006 - CY 2006 FY 2006
You want to see FY 2006 data where FY is the one specific for each
customer.
If this is the case, I think the right model is to have one Fiscal
Calendar dimension (with FY Year, FY MonthNumber, FY QuarterNumber)
that is linked to the fact table.
Of course this model doesn't support you if you want to change the FY
of a Customer without changing the data already loaded into the fact
table.
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
Craig HB wrote:
> I am building a data warehouse using the dimensional model -- i.e. fact and
> dimensional tables in a star schema.
> For my Calendar dimension, I want to include Fiscal periods, but the data
> warehouse has data for different clients that have different fiscal calendars.
> How do I handle that ?

Calendar dimension with multiple Fiscal calendars

I am building a data warehouse using the dimensional model -- i.e. fact and
dimensional tables in a star schema.
For my Calendar dimension, I want to include Fiscal periods, but the data
warehouse has data for different clients that have different fiscal calendar
s.
How do I handle that ?If I understand well, you have:
Customer A - 15/10/2006 - CY 2006 FY 2007
Customer A - 30/09/2006 - CY 2006 FY 2006
Customer B - 15/10/2006 - CY 2006 FY 2006
Customer B - 15/10/2006 - CY 2006 FY 2006
You want to see FY 2006 data where FY is the one specific for each
customer.
If this is the case, I think the right model is to have one Fiscal
Calendar dimension (with FY Year, FY MonthNumber, FY QuarterNumber)
that is linked to the fact table.
Of course this model doesn't support you if you want to change the FY
of a Customer without changing the data already loaded into the fact
table.
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
Craig HB wrote:
> I am building a data warehouse using the dimensional model -- i.e. fact an
d
> dimensional tables in a star schema.
> For my Calendar dimension, I want to include Fiscal periods, but the data
> warehouse has data for different clients that have different fiscal calend
ars.
> How do I handle that ?