Friday, March 23, 2012

Making a create statement from existing database

We have a Peoplesoft application built over MS server 2000. The peoplesoft
application has created a table in ms server via its application designer
where the field types, sizes, indexes are automatically generated from the
data dictionary and search keys etc.
What I would like to know is how can I inquire via query analyser the
structure of these tables ( sort of reverse enginering the SQL DDL statement
s
) so that I can then end up with an SQL create statement etc.
thanks.listTableColumns should get you pretty close:
http://www.aspfaq.com/2177
"JD" <JD@.discussions.microsoft.com> wrote in message
news:56C8824E-D60E-4965-A62F-55B893D8F7F4@.microsoft.com...
> We have a Peoplesoft application built over MS server 2000. The peoplesoft
> application has created a table in ms server via its application designer
> where the field types, sizes, indexes are automatically generated from the
> data dictionary and search keys etc.
> What I would like to know is how can I inquire via query analyser the
> structure of these tables ( sort of reverse enginering the SQL DDL
> statements
> ) so that I can then end up with an SQL create statement etc.
> thanks.|||"JD" <JD@.discussions.microsoft.com> wrote in message
news:56C8824E-D60E-4965-A62F-55B893D8F7F4@.microsoft.com...
> We have a Peoplesoft application built over MS server 2000. The peoplesoft
> application has created a table in ms server via its application designer
> where the field types, sizes, indexes are automatically generated from the
> data dictionary and search keys etc.
> What I would like to know is how can I inquire via query analyser the
> structure of these tables ( sort of reverse enginering the SQL DDL
> statements
> ) so that I can then end up with an SQL create statement etc.
> thanks.
Take a look at INFORMATION_SCHEMA in the Books Online. In particular, you
will want to pay attention to INFORMATION_SCHEMA.TABLES and .COLUMNS.
As for the indexes and so forth, that will be a bit more tricky.
Rick Sawtell
MCT, MCSD, MCDBA|||If you can use Enterprise Manager, there is a wizard that generates scripts.
You can pick the specific table you're interested in and save it creation
script. There's options to include indexes, primary keys, etc.
Joe
"Aaron Bertrand [SQL Server MVP]" wrote:

> listTableColumns should get you pretty close:
> http://www.aspfaq.com/2177
>
> "JD" <JD@.discussions.microsoft.com> wrote in message
> news:56C8824E-D60E-4965-A62F-55B893D8F7F4@.microsoft.com...
>
>|||Try Creating a SQL Script from Enterprise Manager. Save it and then open it
up with Query Analyzer
"JD" <JD@.discussions.microsoft.com> escribi en el mensaje
news:56C8824E-D60E-4965-A62F-55B893D8F7F4@.microsoft.com...
> We have a Peoplesoft application built over MS server 2000. The peoplesoft
> application has created a table in ms server via its application designer
> where the field types, sizes, indexes are automatically generated from the
> data dictionary and search keys etc.
> What I would like to know is how can I inquire via query analyser the
> structure of these tables ( sort of reverse enginering the SQL DDL
> statements
> ) so that I can then end up with an SQL create statement etc.
> thanks.

No comments:

Post a Comment