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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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,

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) said:
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 said:
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
Click to expand...
Click to expand...
 
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


Jan said:
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) said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

Patrice said:
See the sp_helptext stored procedure...

--

Jan said:
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
Click to expand...
Click to expand...
 
Back
Top