How to generate SQL script in diffrent formating ?

How to generate SQL script in diffrent formating ?

Post by John Smit » Wed, 30 Jul 2003 01:24:01



All,

Can I tell to Enterprise Manager to generate sql script as following:

Generate the CREATE <object> command for each object that DOES NOT EXIST.

I know that I can DROP and generate a new object ( under the formatting
tab). But what I need is to generate if the object does not exist ( I would
like to keep the table untouched if they are already there).

Do you think I can do this using Enterprise Manager or I need to use some
other tool.

Thanks in advance

 
 
 

How to generate SQL script in diffrent formating ?

Post by Anthony Zessi » Wed, 30 Jul 2003 01:32:22


If the object does not exist, how is SQL server supposed to know what to
create?
Is it supposed to read you mind? Use ESP?  Talk to God?


Quote:> All,

> Can I tell to Enterprise Manager to generate sql script as following:

> Generate the CREATE <object> command for each object that DOES NOT EXIST.

> I know that I can DROP and generate a new object ( under the formatting
> tab). But what I need is to generate if the object does not exist ( I
would
> like to keep the table untouched if they are already there).

> Do you think I can do this using Enterprise Manager or I need to use some
> other tool.

> Thanks in advance


 
 
 

How to generate SQL script in diffrent formating ?

Post by John Smit » Wed, 30 Jul 2003 01:48:19


Lets say I like to upgrade an existing database in which has table A, B, C,
and D already with a two new tables E and F.

What I like to do is to generate and SQL script that will create A, B, C, D,
E, F on a fresh database
and same script be able to create/add E, F if the database already existed
contained A, B, C, D.

In short to create A, B, C, D objects if they do not exist if yes do not
touch it.

IF ( not EXIST object A)
BEGIN
        Create A
END

Thanks.

> If the object does not exist, how is SQL server supposed to know what to
> create?
> Is it supposed to read you mind? Use ESP?  Talk to God?



> > All,

> > Can I tell to Enterprise Manager to generate sql script as following:

> > Generate the CREATE <object> command for each object that DOES NOT
EXIST.

> > I know that I can DROP and generate a new object ( under the formatting
> > tab). But what I need is to generate if the object does not exist ( I
> would
> > like to keep the table untouched if they are already there).

> > Do you think I can do this using Enterprise Manager or I need to use
some
> > other tool.

> > Thanks in advance

 
 
 

How to generate SQL script in diffrent formating ?

Post by Bob Barrow » Wed, 30 Jul 2003 01:58:48


When EM generates scripts, it already generates the code to check for the
table's existance. It looks like this, usually:

if table exists
begin
    drop table
    print message
end
go
create table ...

Just modify it so that it looks like this:
if table exists
begin
    print 'table exists message' --optional
end
else
create table ...


> Lets say I like to upgrade an existing database in which has table A,
> B, C, and D already with a two new tables E and F.

> What I like to do is to generate and SQL script that will create A,
> B, C, D, E, F on a fresh database
> and same script be able to create/add E, F if the database already
> existed contained A, B, C, D.

> In short to create A, B, C, D objects if they do not exist if yes do
> not touch it.

> IF ( not EXIST object A)
> BEGIN
>         Create A
> END

> Thanks.


>> If the object does not exist, how is SQL server supposed to know
>> what to create?
>> Is it supposed to read you mind? Use ESP?  Talk to God?



>>> All,

>>> Can I tell to Enterprise Manager to generate sql script as
>>> following:

>>> Generate the CREATE <object> command for each object that DOES NOT
>>> EXIST.

>>> I know that I can DROP and generate a new object ( under the
>>> formatting tab). But what I need is to generate if the object does
>>> not exist ( I would like to keep the table untouched if they are
>>> already there).

>>> Do you think I can do this using Enterprise Manager or I need to
>>> use some other tool.

>>> Thanks in advance

 
 
 

How to generate SQL script in diffrent formating ?

Post by Anthony Zessi » Wed, 30 Jul 2003 02:08:11


IF NOT EXISTS(Select * FROM sysobjects where name = 'Anthony' and Type =
'U')
BEGIN
     Print 'Table does not exist'
END


> Lets say I like to upgrade an existing database in which has table A, B,
C,
> and D already with a two new tables E and F.

> What I like to do is to generate and SQL script that will create A, B, C,
D,
> E, F on a fresh database
> and same script be able to create/add E, F if the database already existed
> contained A, B, C, D.

> In short to create A, B, C, D objects if they do not exist if yes do not
> touch it.

> IF ( not EXIST object A)
> BEGIN
>         Create A
> END

> Thanks.


> > If the object does not exist, how is SQL server supposed to know what to
> > create?
> > Is it supposed to read you mind? Use ESP?  Talk to God?



> > > All,

> > > Can I tell to Enterprise Manager to generate sql script as following:

> > > Generate the CREATE <object> command for each object that DOES NOT
> EXIST.

> > > I know that I can DROP and generate a new object ( under the
formatting
> > > tab). But what I need is to generate if the object does not exist ( I
> > would
> > > like to keep the table untouched if they are already there).

> > > Do you think I can do this using Enterprise Manager or I need to use
> some
> > > other tool.

> > > Thanks in advance

 
 
 

How to generate SQL script in diffrent formating ?

Post by John Smit » Wed, 30 Jul 2003 02:09:29


I was hopping NOT do that modification by hand and let the tool ( or any
other one) to do that. That is why I have posted this question.

Thanks any way.


> When EM generates scripts, it already generates the code to check for the
> table's existance. It looks like this, usually:

> if table exists
> begin
>     drop table
>     print message
> end
> go
> create table ...

> Just modify it so that it looks like this:
> if table exists
> begin
>     print 'table exists message' --optional
> end
> else
> create table ...


> > Lets say I like to upgrade an existing database in which has table A,
> > B, C, and D already with a two new tables E and F.

> > What I like to do is to generate and SQL script that will create A,
> > B, C, D, E, F on a fresh database
> > and same script be able to create/add E, F if the database already
> > existed contained A, B, C, D.

> > In short to create A, B, C, D objects if they do not exist if yes do
> > not touch it.

> > IF ( not EXIST object A)
> > BEGIN
> >         Create A
> > END

> > Thanks.


> >> If the object does not exist, how is SQL server supposed to know
> >> what to create?
> >> Is it supposed to read you mind? Use ESP?  Talk to God?



> >>> All,

> >>> Can I tell to Enterprise Manager to generate sql script as
> >>> following:

> >>> Generate the CREATE <object> command for each object that DOES NOT
> >>> EXIST.

> >>> I know that I can DROP and generate a new object ( under the
> >>> formatting tab). But what I need is to generate if the object does
> >>> not exist ( I would like to keep the table untouched if they are
> >>> already there).

> >>> Do you think I can do this using Enterprise Manager or I need to
> >>> use some other tool.

> >>> Thanks in advance

 
 
 

How to generate SQL script in diffrent formating ?

Post by John Smit » Wed, 30 Jul 2003 02:17:13


Yes Anthony,  I know!
But how do you tell Enterprise Manager to generate this ? that is the
question.

Thanks.


> IF NOT EXISTS(Select * FROM sysobjects where name = 'Anthony' and Type =
> 'U')
> BEGIN
>      Print 'Table does not exist'
> END



> > Lets say I like to upgrade an existing database in which has table A, B,
> C,
> > and D already with a two new tables E and F.

> > What I like to do is to generate and SQL script that will create A, B,
C,
> D,
> > E, F on a fresh database
> > and same script be able to create/add E, F if the database already
existed
> > contained A, B, C, D.

> > In short to create A, B, C, D objects if they do not exist if yes do not
> > touch it.

> > IF ( not EXIST object A)
> > BEGIN
> >         Create A
> > END

> > Thanks.


> > > If the object does not exist, how is SQL server supposed to know what
to
> > > create?
> > > Is it supposed to read you mind? Use ESP?  Talk to God?



> > > > All,

> > > > Can I tell to Enterprise Manager to generate sql script as
following:

> > > > Generate the CREATE <object> command for each object that DOES NOT
> > EXIST.

> > > > I know that I can DROP and generate a new object ( under the
> formatting
> > > > tab). But what I need is to generate if the object does not exist
( I
> > > would
> > > > like to keep the table untouched if they are already there).

> > > > Do you think I can do this using Enterprise Manager or I need to use
> > some
> > > > other tool.

> > > > Thanks in advance

 
 
 

How to generate SQL script in diffrent formating ?

Post by Anthony Zessi » Wed, 30 Jul 2003 02:23:17


I see.  Sorry for misunderstanding you question...


> Yes Anthony,  I know!
> But how do you tell Enterprise Manager to generate this ? that is the
> question.

> Thanks.



> > IF NOT EXISTS(Select * FROM sysobjects where name = 'Anthony' and Type =
> > 'U')
> > BEGIN
> >      Print 'Table does not exist'
> > END



> > > Lets say I like to upgrade an existing database in which has table A,
B,
> > C,
> > > and D already with a two new tables E and F.

> > > What I like to do is to generate and SQL script that will create A, B,
> C,
> > D,
> > > E, F on a fresh database
> > > and same script be able to create/add E, F if the database already
> existed
> > > contained A, B, C, D.

> > > In short to create A, B, C, D objects if they do not exist if yes do
not
> > > touch it.

> > > IF ( not EXIST object A)
> > > BEGIN
> > >         Create A
> > > END

> > > Thanks.


> > > > If the object does not exist, how is SQL server supposed to know
what
> to
> > > > create?
> > > > Is it supposed to read you mind? Use ESP?  Talk to God?



> > > > > All,

> > > > > Can I tell to Enterprise Manager to generate sql script as
> following:

> > > > > Generate the CREATE <object> command for each object that DOES NOT
> > > EXIST.

> > > > > I know that I can DROP and generate a new object ( under the
> > formatting
> > > > > tab). But what I need is to generate if the object does not exist
> ( I
> > > > would
> > > > > like to keep the table untouched if they are already there).

> > > > > Do you think I can do this using Enterprise Manager or I need to
use
> > > some
> > > > > other tool.

> > > > > Thanks in advance

 
 
 

How to generate SQL script in diffrent formating ?

Post by Bob Barrow » Wed, 30 Jul 2003 02:32:21


Well, then, the answer is: you can't.

> I was hopping NOT do that modification by hand and let the tool ( or
> any other one) to do that. That is why I have posted this question.

> Thanks any way.



>> When EM generates scripts, it already generates the code to check
>> for the table's existance. It looks like this, usually:

>> if table exists
>> begin
>>     drop table
>>     print message
>> end
>> go
>> create table ...

>> Just modify it so that it looks like this:
>> if table exists
>> begin
>>     print 'table exists message' --optional
>> end
>> else
>> create table ...


>>> Lets say I like to upgrade an existing database in which has table
>>> A, B, C, and D already with a two new tables E and F.

>>> What I like to do is to generate and SQL script that will create A,
>>> B, C, D, E, F on a fresh database
>>> and same script be able to create/add E, F if the database already
>>> existed contained A, B, C, D.

>>> In short to create A, B, C, D objects if they do not exist if yes do
>>> not touch it.

>>> IF ( not EXIST object A)
>>> BEGIN
>>>         Create A
>>> END

>>> Thanks.


>>>> If the object does not exist, how is SQL server supposed to know
>>>> what to create?
>>>> Is it supposed to read you mind? Use ESP?  Talk to God?



>>>>> All,

>>>>> Can I tell to Enterprise Manager to generate sql script as
>>>>> following:

>>>>> Generate the CREATE <object> command for each object that DOES NOT
>>>>> EXIST.

>>>>> I know that I can DROP and generate a new object ( under the
>>>>> formatting tab). But what I need is to generate if the object does
>>>>> not exist ( I would like to keep the table untouched if they are
>>>>> already there).

>>>>> Do you think I can do this using Enterprise Manager or I need to
>>>>> use some other tool.

>>>>> Thanks in advance

 
 
 

How to generate SQL script in diffrent formating ?

Post by Bob Barrow » Wed, 30 Jul 2003 02:48:41


I take that back: there is nothing built-in that does this. However, that is
not to say that somebody somewhere does not have a script that does this
posted on a website somewhere. You might have success with a Google search.

Bob Barrows


> Well, then, the answer is: you can't.


>> I was hopping NOT do that modification by hand and let the tool ( or
>> any other one) to do that. That is why I have posted this question.

>> Thanks any way.



>>> When EM generates scripts, it already generates the code to check
>>> for the table's existance. It looks like this, usually:

>>> if table exists
>>> begin
>>>     drop table
>>>     print message
>>> end
>>> go
>>> create table ...

>>> Just modify it so that it looks like this:
>>> if table exists
>>> begin
>>>     print 'table exists message' --optional
>>> end
>>> else
>>> create table ...


>>>> Lets say I like to upgrade an existing database in which has table
>>>> A, B, C, and D already with a two new tables E and F.

>>>> What I like to do is to generate and SQL script that will create A,
>>>> B, C, D, E, F on a fresh database
>>>> and same script be able to create/add E, F if the database already
>>>> existed contained A, B, C, D.

>>>> In short to create A, B, C, D objects if they do not exist if yes
>>>> do not touch it.

>>>> IF ( not EXIST object A)
>>>> BEGIN
>>>>         Create A
>>>> END

>>>> Thanks.


>>>>> If the object does not exist, how is SQL server supposed to know
>>>>> what to create?
>>>>> Is it supposed to read you mind? Use ESP?  Talk to God?



>>>>>> All,

>>>>>> Can I tell to Enterprise Manager to generate sql script as
>>>>>> following:

>>>>>> Generate the CREATE <object> command for each object that DOES
>>>>>> NOT EXIST.

>>>>>> I know that I can DROP and generate a new object ( under the
>>>>>> formatting tab). But what I need is to generate if the object
>>>>>> does not exist ( I would like to keep the table untouched if
>>>>>> they are already there).

>>>>>> Do you think I can do this using Enterprise Manager or I need to
>>>>>> use some other tool.

>>>>>> Thanks in advance

 
 
 

How to generate SQL script in diffrent formating ?

Post by Jacco Schalkwij » Wed, 30 Jul 2003 10:39:31


Hi John,

SQLCompare from RedGate (www.red-gate.com) is a quite affordable tool that
has this functionality, and quite a lot more.

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


> I was hopping NOT do that modification by hand and let the tool ( or any
> other one) to do that. That is why I have posted this question.

> Thanks any way.



> > When EM generates scripts, it already generates the code to check for
the
> > table's existance. It looks like this, usually:

> > if table exists
> > begin
> >     drop table
> >     print message
> > end
> > go
> > create table ...

> > Just modify it so that it looks like this:
> > if table exists
> > begin
> >     print 'table exists message' --optional
> > end
> > else
> > create table ...


> > > Lets say I like to upgrade an existing database in which has table A,
> > > B, C, and D already with a two new tables E and F.

> > > What I like to do is to generate and SQL script that will create A,
> > > B, C, D, E, F on a fresh database
> > > and same script be able to create/add E, F if the database already
> > > existed contained A, B, C, D.

> > > In short to create A, B, C, D objects if they do not exist if yes do
> > > not touch it.

> > > IF ( not EXIST object A)
> > > BEGIN
> > >         Create A
> > > END

> > > Thanks.


> > >> If the object does not exist, how is SQL server supposed to know
> > >> what to create?
> > >> Is it supposed to read you mind? Use ESP?  Talk to God?



> > >>> All,

> > >>> Can I tell to Enterprise Manager to generate sql script as
> > >>> following:

> > >>> Generate the CREATE <object> command for each object that DOES NOT
> > >>> EXIST.

> > >>> I know that I can DROP and generate a new object ( under the
> > >>> formatting tab). But what I need is to generate if the object does
> > >>> not exist ( I would like to keep the table untouched if they are
> > >>> already there).

> > >>> Do you think I can do this using Enterprise Manager or I need to
> > >>> use some other tool.

> > >>> Thanks in advance

 
 
 

1. Generate MS-ACCESS table using SQL Script generated by SQL Server

PLATEFORM

UI Tool - VC++ 6.0
Database - MS-ACCESS, SQL Server

Hello,

I am using MS-ACCESS database into my project. I am creating SQL script
using SQL Server. I want to use same script to generate MS-ACCESS database
tables.

For example given script is generated by SQL Server and I want to use same
script to create table into MS-ACCESS database.

----------------------------------------------------------------------------
----------------------------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Order_Details_Orders]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Order Details] DROP CONSTRAINT FK_Order_Details_Orders
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Orders]
GO

CREATE TABLE [dbo].[Orders] (
 [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
 [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [EmployeeID] [int] NULL ,
 [OrderDate] [datetime] NULL ,
 [RequiredDate] [datetime] NULL ,
 [ShippedDate] [datetime] NULL ,
 [ShipVia] [int] NULL ,
 [Freight] [money] NULL ,
 [ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
 [ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Kindly let me know if any one knows how can it be done.

Thanks in advance.

Aarti

2. 4GL question

3. SQL Script to generate SQL Scripts???

4. How to trace prepared SQL statements

5. Formating SQL generated reports.

6. LATCH_EX and CXPACKET

7. generate SQL scripts from a script or stored procedure

8. Filtering data for a merge subscription

9. Generate SQL script in SQL 2000 for SQL 6.5

10. Formating messages generated by xp_sendmail

11. SQL 6.5 Generating SQL Scripts.

12. Generate SQL Script for data of SQL Server table

13. Generate SQL script for all objects in a database - but using ADO,not SQL-DMO