Script to create Stored Procedures
Benjamin von Ullrich
defncopy utility. in ~sybase/bin (UNIX -- you didn't mention your
platform, something you should ALWAYS do in these types of messages).
read about it in the documentation:
http://cobweb.sybase.com:8011/srv11003/unixbook/2733
--
...ben
Benjamin von Ullrich Benjamin_v...@hotwired.com
HotWired, Inc. http://www.hotwired.com/staff/drben/
(Formerly of Sybase, Inc.)
NNEHA
We are trying to build a new database from the existing one on a different
server. I tried to get the scripts of existing Stored Procedures from
syscomments. But the output has to be formatted. I am not sure how to do
this.
If anyone has come across such procedure which extracts data from
syscomments and formats the output, please help me.
Thanks
You can send reply to nn...@aol.com
Richard Scranton
> If anyone has come across such procedure which extracts data from
> syscomments and formats the output, please help me.
>
>
> You can send reply to nn...@aol.com
Look at "defncopy" for your platform.
cc: nn...@aol.com
----
========================================
Richard Scranton - LDA Systems, Columbus
scr...@ix.netcom.com
am...@nacm.com
scr...@ix.netcom.com (Richard Scranton) wrote:
Responses to this query mentioned the Sybase utility defncopy. A
word of caution: Even this utility can mangle the formatting of the
procedure. I've extracted the text for a procedure and tried to
re-create the procedure, only to find that defncopy had joined the
name of a table with the word preceding it (the space separating the
two words disappeared).
Naturally, I received the error message that the table does not exist.
I must say that this has happened to me only once after years of using
defncopy.
Mike Hsu
/*********************************************************************
* Stored procedure sp_helpcode for Sybase or Microsoft SQL Server
* Author: Andrew Zanevsky, AZ Databases, Inc.
* Date: March 16, 1995
* Description: Works similar to standard system stored procedure
* sp_helptext. Correclty handles cases when a substring
* begins in one row of syscomments table and continues
* in the next (no split lines!).
* Uses print command (not select) to generate the result
* for technical reasons.
* Parameters: - @objname - object name
**********************************************************************/
use sybsystemprocs
go
drop procedure sp_helpcode
go
create procedure sp_helpcode
@objname varchar(92)
as
declare @text_count int,
@text varchar(255),
@line varchar(255),
@split tinyint,
@lf char(1)
select @lf = char(10)
if @@trancount = 0
begin
set transaction isolation level 1
set chained off
end
/***** Make sure the @objname is local to the current database. */
if @objname like "%.%.%" and
substring(@objname, 1, charindex(".", @objname) - 1) !=
db_name()
begin
print "Object must be in the current database."
return (1)
end
/***** See if @objname exists. */
if (object_id(@objname) is NULL)
begin
print "Object does not exist in this database."
return (1)
end
if exists(select name from sysobjects where name = @objname
and type = "P")
begin
print 'if exists(select name from sysobjects where name="%1!")',@objname
print 'DROP PROCEDURE %1!',@objname
print 'go'
if exists(select name from sysobjects where name = @objname
and type = "V")
begin
print 'if exists(select name from sysobjects where name="%1!")',@objname
print 'DROP VIEW %1!',@objname
print 'go'
end
select text
into #text
from syscomments
where id = object_id(@objname)
select @text_count = @@rowcount
/***** Parse and print the text one line at a time. */
set rowcount 1
while @text_count > 0
begin
select @text_count = @text_count - 1,
@text = text + space( ( 255 - datalength( text ) )
* sign( @text_count ) ),
@split = charindex( @lf, text )
from #text
delete #text
while @split > 0
begin
select @line = @line + substring( @text, 1, @split - 1
),
@text = right( @text, datalength( @text ) -
@split )
print "%1!", @line
select @split = charindex( @lf, @text ),
@line = NULL
end
if @text_count = 0
print "%1!", @text
else
select @line = @text
end
print "go"
go
grant execute on sp_helpcode to public
go
Richard Scranton
> scr...@ix.netcom.com (Richard Scranton) wrote:
>
> Responses to this query mentioned the Sybase utility defncopy. A
> word of caution: Even this utility can mangle the formatting of the
> procedure. I've extracted the text for a procedure and tried to
> re-create the procedure, only to find that defncopy had joined the
> name of a table with the word preceding it (the space separating the
> two words disappeared).
>
> Naturally, I received the error message that the table does not exist.
>
> I must say that this has happened to me only once after years of using
> defncopy.
>
>
I too ran across this problem recently while duping a databases schema to a
test bed database. The solution I came up with was more a hack than a
solution, but worked for the moment. The glitch in defncopy seems to be
server-side rather than client-side, as my defncopy is dated 10/27/1994 even
after a roll-up to 10.2.5 ebf 6121. Since I was moving an enormous number of
tables, procs, triggers, indices, and primary key declarations, I made
everything pass through a "sed" process to correct the syntax errors introduced
by the faulty reconstruction of the text. If this is a reported bug with a
resolution, I wouldn't mind knowing about it.
Bret Halford
In article <54t7o0$c...@newsbf02.news.aol.com>, nn...@aol.com (NNEHA) writes:
|> We are trying to build a new database from the existing one on a different
|> server. I tried to get the scripts of existing Stored Procedures from
|> syscomments. But the output has to be formatted. I am not sure how to do
|> this.
|>
|> syscomments and formats the output, please help me.
|>
I take it the new server is on a different OS/Platform or that you
want the new database to be smaller? Otherwise just loading
a dump of the database and dropping unwanted objects would probably
be easier. Not that I want to discourage you from keeping scripts
for all your objects around, it is a good idea to do so.
The "defncopy" utility provided by Sybase will extract scripts for
procedures, triggers, etc (just about everything but table definitions).
The documentation is in the "Utility Programs for UNIX" manual, or
in Answerbase.
--
---------------------------------------------------------------------
| Bret Halford br...@sybase.com ___|
| Sybase Technical Support fax (303)-486-7831 __|
| 6400 S. Fiddlers Green Circle |__
| Englewood, CO 80111-4954 USA exec sp_realitycheck() |___
| |
#####################################################################
John Meyer
These will not help you.
You need to take care of much more then just procedure code when trying to
get a procedure definition to be applied elsewhere.
You need to get the current permission settings, auditing options, current
state of the database about commit/auto commit and so on... When you will
apply back your procedure code, these need to be applied as well or you
don't have the real thing.
Check the output of a stored procedure extraction for a Stored Procedure
from SQL-Programmer for Windows (works with SYBASE and MS SQL Server, all
versions, all platforms).
Check http://www.sfi-software.com to download a copy and see. Let me know
if you need anything else.
John Meyer,
Sylvain Faust Inc. (SFI)
http://www.sfi-software.com
Sylvain Faust Inc. Makes Tools for the Database Server Specialist since
1989
Richard Scranton <scr...@ix.netcom.com> wrote in article
<N.102996.085104.80@scrantr>...
Ranga Gopalan
Hi,
You *ought* to have SQL script files for creating your stored procedures.
In some cases where you have long comments prior to the creation of the
stored procedure you could have junk information in syscomments for the
stored procedure text.
e.g. The following stuff in a sql script used to create a stored procedure
could result in incorrect information being stored in syscomments. The
procedure will be created correctly and will run correctly but the
information in syscomments will be incorrect.
/*
***
*** Large comment more than 512 (say) characters
***
*/
create procedure procedure_name
as
begin
end
go
Hope this is useful.
Ranga.
Bret Halford <br...@sybase.com> wrote in article
<558ibb$a...@fyi.sybase.com>...
> In article <54t7o0$c...@newsbf02.news.aol.com>, nn...@aol.com (NNEHA)
writes:
>
> < ...Deleted ... >