how to retrieve ddl script from a db object..?

22 Feb 2006 3:28 PM

Jan

Hi,

How to get (plain text) ddl script for databese objects in a sql server db..?

for example:

CREATE TABLE [dbo].[T_BatchLog] (
[ndBatchLog] int IDENTITY(1, 1) NOT NULL,
[ndBatchRun] int NOT NULL,
[ddDate] datetime NOT NULL,
[sdDescription] varchar(200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_BatchLog] PRIMARY KEY CLUSTERED ([ndBatchLog]),
CONSTRAINT [FK_BatchLog_BatchRun] FOREIGN KEY ([ndBatchRun])
REFERENCES [dbo].[T_BatchRun] ([ndBatchRun])
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
ON [PRIMARY]
GO

23 Feb 2006 2:11 AM

Val Mazur (MVP)

Jan,

I do not think SQL Server keeps all DDL scripts inside. It could keep script
for the stored procedure, but I do not see any reason that it suppose to
keep table definition scripts. I believe SQL Server generates scripts from
the information about the tables

--
Val Mazur
Microsoft MVP
http://xport.mvps.org

Show quote

"Jan" <J**@discussions.microsoft.com> wrote in message
news:5CEFCCB7-3CDD-4F58-8C74-6DB90BD55653@microsoft.com...
> Hi,
>
> How to get (plain text) ddl script for databese objects in a sql server
> db..?
>
> for example:
>
> CREATE TABLE [dbo].[T_BatchLog] (
> [ndBatchLog] int IDENTITY(1, 1) NOT NULL,
> [ndBatchRun] int NOT NULL,
> [ddDate] datetime NOT NULL,
> [sdDescription] varchar(200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL,
> CONSTRAINT [PK_BatchLog] PRIMARY KEY CLUSTERED ([ndBatchLog]),
> CONSTRAINT [FK_BatchLog_BatchRun] FOREIGN KEY ([ndBatchRun])
> REFERENCES [dbo].[T_BatchRun] ([ndBatchRun])
> ON UPDATE NO ACTION
> ON DELETE NO ACTION
> )
> ON [PRIMARY]
> GO

23 Feb 2006 8:19 AM

Jan

Val,

Thanks for your reply.

I think you are right and thats why i am asking this.

Can this be done in vb or in c#???

Jan

Show quote

"Val Mazur (MVP)" wrote:

> Jan,
>
> I do not think SQL Server keeps all DDL scripts inside. It could keep script
> for the stored procedure, but I do not see any reason that it suppose to
> keep table definition scripts. I believe SQL Server generates scripts from
> the information about the tables
>
> --
> Val Mazur
> Microsoft MVP
> http://xport.mvps.org
>
>
> "Jan" <J**@discussions.microsoft.com> wrote in message
> news:5CEFCCB7-3CDD-4F58-8C74-6DB90BD55653@microsoft.com...
> > Hi,
> >
> > How to get (plain text) ddl script for databese objects in a sql server
> > db..?
> >
> > for example:
> >
> > CREATE TABLE [dbo].[T_BatchLog] (
> > [ndBatchLog] int IDENTITY(1, 1) NOT NULL,
> > [ndBatchRun] int NOT NULL,
> > [ddDate] datetime NOT NULL,
> > [sdDescription] varchar(200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> > NULL,
> > CONSTRAINT [PK_BatchLog] PRIMARY KEY CLUSTERED ([ndBatchLog]),
> > CONSTRAINT [FK_BatchLog_BatchRun] FOREIGN KEY ([ndBatchRun])
> > REFERENCES [dbo].[T_BatchRun] ([ndBatchRun])
> > ON UPDATE NO ACTION
> > ON DELETE NO ACTION
> > )
> > ON [PRIMARY]
> > GO
>
>
>

23 Feb 2006 11:21 AM

Val Mazur (MVP)

Jan,

You could query system tables from the SQL Server and construct your own DDL
scripts based on information from those tables. It could be huge task and
you would need to go through a lot of scenarios. It is quite possible that
there is a third party component exists that does this task. Maybe you could
avoid it at all if it is not a very frequent task. If it is not very
frequent, then you could use SQL Enterprise Manager to do it manually once
in a while. What do you need to achieve?

--
Val Mazur
Microsoft MVP
http://xport.mvps.org

Show quote

"Jan" <J**@discussions.microsoft.com> wrote in message
news:144EEFBA-684F-4A4C-9826-AADD8EEF19A5@microsoft.com...
> Val,
>
> Thanks for your reply.
>
> I think you are right and thats why i am asking this.
>
> Can this be done in vb or in c#???
>
> Jan
>
> "Val Mazur (MVP)" wrote:
>
>> Jan,
>>
>> I do not think SQL Server keeps all DDL scripts inside. It could keep
>> script
>> for the stored procedure, but I do not see any reason that it suppose to
>> keep table definition scripts. I believe SQL Server generates scripts
>> from
>> the information about the tables
>>
>> --
>> Val Mazur
>> Microsoft MVP
>> http://xport.mvps.org
>>
>>
>> "Jan" <J**@discussions.microsoft.com> wrote in message
>> news:5CEFCCB7-3CDD-4F58-8C74-6DB90BD55653@microsoft.com...
>> > Hi,
>> >
>> > How to get (plain text) ddl script for databese objects in a sql server
>> > db..?
>> >
>> > for example:
>> >
>> > CREATE TABLE [dbo].[T_BatchLog] (
>> > [ndBatchLog] int IDENTITY(1, 1) NOT NULL,
>> > [ndBatchRun] int NOT NULL,
>> > [ddDate] datetime NOT NULL,
>> > [sdDescription] varchar(200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>> > NULL,
>> > CONSTRAINT [PK_BatchLog] PRIMARY KEY CLUSTERED ([ndBatchLog]),
>> > CONSTRAINT [FK_BatchLog_BatchRun] FOREIGN KEY ([ndBatchRun])
>> > REFERENCES [dbo].[T_BatchRun] ([ndBatchRun])
>> > ON UPDATE NO ACTION
>> > ON DELETE NO ACTION
>> > )
>> > ON [PRIMARY]
>> > GO
>>
>>
>>

23 Feb 2006 1:31 PM

Patrice

See the sp_helptext stored procedure...

--

Show quote

"Jan" <J**@discussions.microsoft.com> a écrit dans le message de
news:5CEFCCB7-3CDD-4F58-8C74-6DB90BD55653@microsoft.com...
> Hi,
>
> How to get (plain text) ddl script for databese objects in a sql server
db..?
>
> for example:
>
> CREATE TABLE [dbo].[T_BatchLog] (
>   [ndBatchLog] int IDENTITY(1, 1) NOT NULL,
>   [ndBatchRun] int NOT NULL,
>   [ddDate] datetime NOT NULL,
>   [sdDescription] varchar(200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
>   CONSTRAINT [PK_BatchLog] PRIMARY KEY CLUSTERED ([ndBatchLog]),
>   CONSTRAINT [FK_BatchLog_BatchRun] FOREIGN KEY ([ndBatchRun])
>   REFERENCES [dbo].[T_BatchRun] ([ndBatchRun])
>   ON UPDATE NO ACTION
>   ON DELETE NO ACTION
> )
> ON [PRIMARY]
> GO

23 Feb 2006 1:41 PM

Patrice

That takes care only of stored procedures of course ;-)

For a table I would trace what happens when you create a script using
Enterprise Manager so see if there is something you could reuse. Else the
last resort would be to use Information_Schema tables that contains AFAIK
all you need.

--
Patrice

Show quote

"Patrice" <a@bc.c> a écrit dans le message de
news:%23OIdm1HOGHA.1124@TK2MSFTNGP10.phx.gbl...
> See the sp_helptext stored procedure...
>
> --
>
> "Jan" <J**@discussions.microsoft.com> a écrit dans le message de
> news:5CEFCCB7-3CDD-4F58-8C74-6DB90BD55653@microsoft.com...
> > Hi,
> >
> > How to get (plain text) ddl script for databese objects in a sql server
> db..?
> >
> > for example:
> >
> > CREATE TABLE [dbo].[T_BatchLog] (
> >   [ndBatchLog] int IDENTITY(1, 1) NOT NULL,
> >   [ndBatchRun] int NOT NULL,
> >   [ddDate] datetime NOT NULL,
> >   [sdDescription] varchar(200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL,
> >   CONSTRAINT [PK_BatchLog] PRIMARY KEY CLUSTERED ([ndBatchLog]),
> >   CONSTRAINT [FK_BatchLog_BatchRun] FOREIGN KEY ([ndBatchRun])
> >   REFERENCES [dbo].[T_BatchRun] ([ndBatchRun])
> >   ON UPDATE NO ACTION
> >   ON DELETE NO ACTION
> > )
> > ON [PRIMARY]
> > GO
>
>