Oracle vs. SQL Server schema compare

I am in a situation where there are two parallel projects (one oracle and on
e
MS SQL) and I need to keep track of the schema differences between each
database. I know there are some tools out there like SQL red gate that
compare between MS SQL but does anyone know of any tool that can do this
across db platforms?You can query the system information tables that SQL Server uses to store
things like objects schemas. The system tables are probably different in
Oracle so the query will be different as well, but the concept is the same.
http://www.aspfaq.com/search.asp?q=schema
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:BEA5052B-6423-41D8-8D0A-558BD31955D7@.microsoft.com...
>I am in a situation where there are two parallel projects (one oracle and
>one
> MS SQL) and I need to keep track of the schema differences between each
> database. I know there are some tools out there like SQL red gate that
> compare between MS SQL but does anyone know of any tool that can do this
> across db platforms?|||>From your requirements, it seems like the open-source SchemaCrawler
tool will do what you need. SchemaCrawler can output details of your
schema (tables, views, procedures, and more) in a standard and uniform
way across different DBMS systems, such as Oracle and SQL Server.
SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available on SourceForge,
at:
http://schemacrawler.sourceforge.net/
You can use a standard diff program to diff the current output with a
reference version of the output. SchemaCrawler can be run either from
the command line, or as an ant task. A lot of examples are available
with the download to help you get started. You will need to provide a
JDBC driver for your database. No other third-party jars are required.
Sualeh Fatehi.

Oracle to Sql Server conversion

Something went a bit wrong. Converted Oracle 10g to Sql Server 2000.
Now, in the schema there is a column that allows nulls.
In Oracle if you look at the column, you see NULL.
However if I look at the same created in Sql Server it actually does not have a <NULL> value there.
When the conversion was done however, all of these NULLs from Oracle came across into Sql Server with the actual value of <NULL>.
This causes the problem that these objects are now no longer displayed within my application. They show up fine using Oracle, and if I use Sql Server from the start it is fine -- the column is just blank, not acutally NULL. I can force the <NULL> value by doing the ctrl+0 on the field, and that breaks it as well.

The column has to allow nulls, but the actual value cannot be <NULL> (in Sql Server). Any suggestions on getting rid of the NULL - I could do an update, but it actually just has to be blank rather than having a value. I tried an update to set it to ' ' but that didn't really work - here was my statement:
update [table] set [columnname] = '' where [columnname] = '<NULL>'

Any other suggestions besides try again? But if it has to be 'try the conversion again' then that's the answer.
Thanks muchupdate [table] set [columnname] = NULL where [columnname] = '<NULL>'

Oracle SQL vs MSSQL SQL

How can I list out all table name of within a schema in MSSQL2005?

In Oracle, I can run this SQL to get all table_name or object_name of a users.

SQL:

SELECT TABLE_NAME FROM USER_TABLES;

SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER='<DB_USERNAME>';

SELECT OBJECT_NAME FROM USER_OBJECTS;

SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER='<DB_USERNAME>';

How to do it in MSSQL2005? any SQL cmd to execute to get the table / object list?

How to do it in MS SQL Server management studio?

I wanna have a list of table / objects under a schema by one action/script, but I DO NOT wanna to use MS SQL server management studio, check table / object name one by one.

Thanks.

Yes .. It is possible...

Select * from INFORMATION_SCHEMA.TABLES where table_schema='dbo'

Oracle query with &

I have a SQL task that I want to execute a query like:

SELECT firstField FROM schema.table WHERE fieldName = 'M&M'

The problem is when I put that in, it won't run.

For SQL*Plus I can stick SET DEFINE OFF before it and it work, not so in Integration Services.

The issue apparently is that &M is treated by Oracle as a variable of some sort.

Any help is appreciated.

Try using char(38) ... SELECT firstField FROM schema.table WHERE fieldName = 'M'||char(38)||'M'|||Thanks. That worked as does 'M' || '&' || 'M'. I'm glad I normally just work with SQL Server |||

Larry Charlton wrote:

Thanks. That worked as does 'M' || '&' || 'M'. I'm glad I normally just work with SQL Server

Do you work at a chocolate manufacturer Larry

http://www.m-ms.com/

-Jamie