generating DDL

In Oracle, there is a system packaged function that returns the DDL for a
database object (DBMS_METADATA.GET_DDL). What is the equivalent in SQL
Server?you can generate DDL from an object using Query Analyzer by
right-clicking the object in the object browser and selecting "Script
Object to <dest> as <action>"
- Baileys
arch wrote:
> In Oracle, there is a system packaged function that returns the DDL for a
> database object (DBMS_METADATA.GET_DDL). What is the equivalent in SQL
> Server?
>|||Yes, I know. But I wanted the DDL to be used in a query. I need a function
or something that returns so I can use it in an SQL statement.
"Baileys" <no_email@.server.com> wrote in message
news:%23%23B7GGLhGHA.3956@.TK2MSFTNGP02.phx.gbl...
> you can generate DDL from an object using Query Analyzer by right-clicking
> the object in the object browser and selecting "Script Object to <dest> as
> <action>"
> - Baileys
> arch wrote:|||You can use SQL DMO to get a table object and then use the script function t
o
script out the table.
You can search for the script function in Books Online. If you need this as
a function look for activating COM objects though the use of extended stored
procedures.
Arun
"arch" wrote:

> Yes, I know. But I wanted the DDL to be used in a query. I need a functi
on
> or something that returns so I can use it in an SQL statement.
> "Baileys" <no_email@.server.com> wrote in message
> news:%23%23B7GGLhGHA.3956@.TK2MSFTNGP02.phx.gbl...
>
>|||arch wrote:
> Yes, I know. But I wanted the DDL to be used in a query. I need a functi
on
> or something that returns so I can use it in an SQL statement.
>
You can't directly generate SQL from within SQL code itself.
As another poster said, you can use DMO, which is a COM object (aka
ActiveX), with some surrounding code, to build your SQL statements,
then submit the to the database.
Or, in some cases you could roll your own sql-building code that would
work within SQL. If you're interested in tables, for example, the
metatable INFORMATION_SCHEMA.COLUMNS can give you column names, data
types and lengths; INFORMATION_SCHEME.CONSTRAINT_COLUMN_USAGE gives
information about which columns are involved in constraints, etc. That
would be a particularly tedious path to have to follow, in my opinion.