Grid display: adatabase

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.

Is there an easy way to loop through every stored procedure in a
database and create a file containing all of the SP code?
lq> Is there an easy way to loop through every stored
> procedure in a database and create a file containing
> all of the SP code?
> lq

I would suggest one of three ideas (in order of ease of use and
compatibility across versions of SQL Server):

1. Enterprise Manager can do this for you

2. You can write a program using SQLDMO to do this. If you've never used
SQLDMO before, you'll want to loop through the StoredProcedure objects in
the Database object. You'll also want to look up the SystemObject property
and the Script method. In addition, we've found that it's easier to have
SQLDMO script the actual proc, while we handle adding script for dropping
the procedure before creating and for granting permissions (but this will
depend on how you control SP's and their permissions).

3. See the technique I mentioned in the thread "Search contents of stored
procedures?" for a down-and-dirty (not to mention fragile) technique.

Craig|||Dear Laurenquantrell

By joining sysobject and syscomments table we can get your desired results,
run this query in Query Analyzer (with result in text mode)...
------------------------
SELECT dbo.syscomments.text, dbo.sysobjects.name
FROM dbo.syscomments INNER JOIN
dbo.sysobjects ON dbo.syscomments.id = dbo.sysobjects.id
WHERE (dbo.sysobjects.xtype = 'p')
------------------------
Best of luck!

Saghir Taj

laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0404161302.54219181@.posting.google.com>...
> Is there an easy way to loop through every stored procedure in a
> database and create a file containing all of the SP code?
> lq|||Thank you very much for that. It is very helpful. Now I just need an
easy way to unencrypt all the SPs I encrypted with "With
Encryption"...
lq

Saghir_taj@.hotmail.com (Saghir Taj) wrote in message news:<f575b6ed.0404200602.74253ebc@.posting.google.com>...
> Dear Laurenquantrell
> By joining sysobject and syscomments table we can get your desired results,
> run this query in Query Analyzer (with result in text mode)...
> ------------------------
> SELECT dbo.syscomments.text, dbo.sysobjects.name
> FROM dbo.syscomments INNER JOIN
> dbo.sysobjects ON dbo.syscomments.id = dbo.sysobjects.id
> WHERE (dbo.sysobjects.xtype = 'p')
> ------------------------
> Best of luck!
> Saghir Taj
>
> laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0404161302.54219181@.posting.google.com>...
> > Is there an easy way to loop through every stored procedure in a
> > database and create a file containing all of the SP code?
> > lq|||Dear Laurenquantrell

Saghir Taj

I am afraid that we can not decrypt a encrypted object! till the SQL
2000. so wait till i find any undocmented function or third party tool
which can do the trick...

Best of luck

Me,
Saghir Taj

laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0404210614.674424ee@.posting.google.com>...
> Thank you very much for that. It is very helpful. Now I just need an
> easy way to unencrypt all the SPs I encrypted with "With
> Encryption"...
> lq
> Saghir_taj@.hotmail.com (Saghir Taj) wrote in message news:<f575b6ed.0404200602.74253ebc@.posting.google.com>...
> > Dear Laurenquantrell
> > By joining sysobject and syscomments table we can get your desired results,
> > run this query in Query Analyzer (with result in text mode)...
> > ------------------------
> > SELECT dbo.syscomments.text, dbo.sysobjects.name
> > FROM dbo.syscomments INNER JOIN
> > dbo.sysobjects ON dbo.syscomments.id = dbo.sysobjects.id
> > WHERE (dbo.sysobjects.xtype = 'p')
> > ------------------------
> > Best of luck!
> > Saghir Taj
> > laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0404161302.54219181@.posting.google.com>...
> > > Is there an easy way to loop through every stored procedure in a
> > > database and create a file containing all of the SP code?
> > > lq|||Dear

I am afraid that we can not decrypt a encrypted object! till the SQL
2000. so wait till i find any undocmented function or third party tool
which can do the trick...

Best of luck

Me,
Saghir Taj

I am afraid that we can not decrypt a encrypted object! till the SQL
2000. so wait till i find any undocmented function or third party tool
which can do the trick...

Best of luck

Me,
Saghir Taj

I am afraid that we can not decrypt a encrypted object! till the SQL
2000. so wait till i find any undocmented function or third party tool
which can do the trick...

Best of luck

Me,
Saghir Taj

Monday, March 19, 2012

generating DDL

Wednesday, March 7, 2012

Generate SPs as text

Generate SPs as text

Grid display

Blog Archive

About Me