Board index » delphi » Extracting Stored procedure code from MSSQL 6.0(NT i386)

Extracting Stored procedure code from MSSQL 6.0(NT i386)

I need to extract stored procedure code from our ms-sql6.0 database , I know
it can be done from sql enterprise manager, but I need to do on the fly from
code, preferably from C++ or Delphi, anyone know a way of doing this?

Could any replies be CC'ed to the following mail addresses
seas...@ctron.com
s...@popmail.i-way.co.uk

Many Thanks

Simon Easter

 

Re:Extracting Stored procedure code from MSSQL 6.0(NT i386)


select * from sysComments
 where id = (select id from sysobjects where name = 'yourprocedure')

have a nice day!

Simon Ashley Easter <s...@popmail.i-way.co.uk> wrote in article
<52oa6q$...@lira.i-way.co.uk>...

Quote
> I need to extract stored procedure code from our ms-sql6.0 database , I
know
> it can be done from sql enterprise manager, but I need to do on the fly
from
> code, preferably from C++ or Delphi, anyone know a way of doing this?

> Could any replies be CC'ed to the following mail addresses
> seas...@ctron.com
> s...@popmail.i-way.co.uk

> Many Thanks

> Simon Easter

Re:Extracting Stored procedure code from MSSQL 6.0(NT i386)


In article <52oa6q$...@lira.i-way.co.uk>, s...@popmail.i-way.co.uk (Simon Ashley Easter) wrote:
I need to extract stored procedure code from our ms-sql6.0 database , I know
it can be done from sql enterprise manager, but I need to do on the fly from
code, preferably from C++ or Delphi, anyone know a way of doing this?

Simon,

I've done this, loading a stored procedure into a TMemo.  Here's a bit of code
to give you the idea:

Q := TQuery.Create(Application);

with Q do
  try
    DatabaseName := FDatabaseName;
    SQL.Add('execute spMgr_Read_Definition_SP :ID');
    ParamByName('ID').AsInteger := FID;

    Prepare;
    Open;

    if not Eof then
    begin
      Buffer := StrAlloc(RecordCount * 255 + 1);
      StrCopy(Buffer, '');

      try
        repeat
          StrCat(Buffer,
PChar(AdjustLineBreaks(FieldByName('text').AsString)));
          Next;
        until Eof;

        memStoredProc.SetTextBuf(Buffer);
      finally
        StrDispose(Buffer);
      end;
    end;

    Result := True;
  finally
    Free;
  end;

My stored procedure, spMgr_Read_Definition_SP, is basically the following:

  // Code to get an SQL-Server object ID from my "Definition ID" parameter
  ...

  select  C.text
  from    sysobjects O
  ,       syscomments C
  where   O.id = @ObjectID
  and     O.type = 'P'
  and     C.id = O.id
  order by
          C.colid

The system table syscomments contains the text of all stored procedures,
triggers, etc. in the database.  The field 'text' is a varchar(255) containing
the text, and 'colid' is a sequence number (1,...) for those objects whose
text is longer than 255 characters.

Hope this helps.

Gerard

Gerard M. Averill
Assistant Researcher

Center for Health Systems Research and Analysis
University of Wisconsin, Madison

E-mail: gaver...@chsra.wisc.edu
Phone: (608) 263-1331

Re:Extracting Stored procedure code from MSSQL 6.0(NT i386)


Hi Simon!

Quote
You wrote:
> I need to extract stored procedure code from our ms-sql6.0 database , I know
> it can be done from sql enterprise manager, but I need to do on the fly from
> code, preferably from C++ or Delphi, anyone know a way of doing this?

As the developers of ODBCExpress, the top BDE replacement for ODBC datasources, I have mailed this on to my
Lead Developer of the product.  He will mail you within the next day (if *anyone* knows he'll know...)

ODBCExpress is a native VCL which replaces the BDE and offers solid problem-free access to ODBC
data sources.  The 16bit version is freeware, while the 32bit is shareware.  Performance is excellent and
the software compiles directly into your app - no messy dlls to distribute.

If you're interested, check it out at http://www.adam.co.za

Regards,

Rob
--
Rob McGillivray
Manager: Client/server Software Development
DataSoft - "Voted Top Development House in South Africa - 1994"

Re:Extracting Stored procedure code from MSSQL 6.0(NT i386)


Quote
> You wrote:
> > I need to extract stored procedure code from our ms-sql6.0 database , I
know
> > it can be done from sql enterprise manager, but I need to do on the fly
from
> > code, preferably from C++ or Delphi, anyone know a way of doing this?

[Advert cut (Sorry Rob!)]

This is a complete guess based on stuff I've seen in comp.databases.sybase,
but
it may work in MSSQLServer; try looking in a table called syscomments?

Alternatively, can't you execute "sp_help" and capture it's output? Or am I
on the
wrong (pointy) end of the stick here?

Sorry it's not a reply to the original sender (I can't find the original
msg)

HTH
        Ade

PS: To make it up to Rob for cutting his ad out, I'll just say that
ODBCExpress
really is a great product, especially for programmatic access to ODBC data!
Hope that helps, Rob!

Re:Extracting Stored procedure code from MSSQL 6.0(NT i386)


Quote
>As the developers of ODBCExpress, the top BDE replacement for ODBC
>datasources, I have mailed this on to my
>ODBCExpress is a native VCL which replaces the BDE and blah blah
>blah blah

Please, would you be kind enough to *stop* your adverti{*word*224}t about
ODBCExpress in every message you post. It becomes anoying and boring.

David

--
David BrabaNT,             |  E-mail: David.Brab...@csl.sni.be
Siemens Nixdorf [SNI],     |  CIS:    100337,1733
Centre Software de Lige,  |  X-400:  C=BE;A=RTT;P=SCN;O=SNI;OU1=LGG1;OU2=S1
2, rue des Fories,         |          S=BRABANT;G=DAVID
4020 Lige, BELGIUM        |  HTTP:   www.sni.de       www.csl.sni.be/~david

Other Threads