produce codes 1

[babeo]

Hi

What Store Procedures or SQL command that can extract a structure of a table/db/view... into an SQL code. I don't remember exactly, but something with sp_helptext ? Am I right? If it is so, how it is used?

Thanks

 

[dickiebird]

sp_help is what you need for objects
eg sp_help tablename
sp_help viewname
For database : sp_helpdb
HTH ;-) Dickie Bird
db@dickiebird.freeserve.co.uk

 

[babeo]

Sorry,
I think I did not make it clear.

What I want is to (re)create a db/table/view etc.. similar to a db/table/view that I pick, and instead of writing a code to create it with all properties, I can have Sybase to produce codes by using sp_helptext (not sure), then I can reuse the code. I believe Sybase having a store procedure to extract to codes, which in fact, I can do it using DBArtisan, but unfortunately I don't have DBArtisan now.
Thanks

 

[dickiebird]

Sorry - I'm with you now.
You need the definitions of objects.
sp_helptext is ok for procedures,triggers,views,etc
but not tables !
Utility defncopy is the same (but again, not tables)
(found in /sybase/products/syb???/bin )
Can't you get Embarcadero's DBArtisan trial V6 for a month ?
HTH
;-) Dickie Bird
db@dickiebird.freeserve.co.uk

 

[babeo]

Thanks dickiebird,

that is good to know . Unfortunately, it is the table code that I want, nothing else ! I probably do as you say then, but I already tried DBArtisan trial version twice already ! Don't know if they keep record of my computer session id and window id or not. Will try.

Thanks

 

[JMCraig]

Hi Babeo,

Try Sybase's Sybase Central. Open the DB in question, expand the list of tables, right-click and choose "Create DDL"--you've got it! (It also gives you CREATE statements for indexes as well as for any associated triggers.)

Sybase Central is one of the PC-client pieces of the install. You should be able to find info on how to install it in the Install Guide for your platform (or the on-line version of the docs--see the FAQ on "Where can I read about...?" if you need help finding the on-line manuals).

If that doesn't do it for you, may I suggest that it wouldn't be at all hard to create a stored procedure yourself that would do the trick. You can use sp_help (from sybsystemprocs) as a template of sorts to decode the entries in syscolumns. All you need is a simple loop to go through the entries there in colid order (you could even use a cursor, although I avoid this when possible--which is almost always!). What you'll need to do, probably, is create a temp table with a single varchar(255) column, or something of the like. As you go down through the list of columns, just put the name, type, and so on onto the end of a string that you'll write to that table.

At the top of the loop, put the CREATE TABLE <name> portion through the left paren; at the end of the loop, put a closing paren.

It might look something like this (not everything you want, but enough to get you started):

create procedure sp_helptable
(@tablename varchar (30))
as

if not exists
(select * from sysobjects
where name = @tablename
and type = 'U') begin
declare @msg varchar (255)
select @msg = 'invalid table name : ' + @tablename
select @msg /* could use raiserror if you want to return an error code */
return
end /* if not exists */

/* create table to hold column info */
create table #create_table
(statement_row varchar (255))

insert #create_table (statement_row)
values (&quot;CREATE TABLE &quot; + @tablename + &quot;(&quot

declare @colid smallint,
@colName varchar(30),
@typeName varchar (30),
@nulls bit,
@coldefStr varchar (255)
select @colid = min (colid)
from syscolumns
where id = object_id (@tablename)
/* loop over columns */
while @colid is not null begin
select @colName = c.name, @typeName = t.name,
@nulls = convert (bit, c.status & 8)
from syscolumns c, systypes t
where c.id = object_id (@tablename)
and c.colid = @colid

select @coldefStr = @colName + char (9) /* tab */
+ @typeName + char (9)
if @nulls = 0 /* nulls not allowed */
select @coldefStr + 'NOT NULL'
/* if nulls are allowed, just leave off */

insert #create_table (statement_row)
values (@coldefStr)

select @colid = min (colid)
from syscolumns
where id = object_id (@tablename)
and colid > @colid
end /* while */

insert #create_table (statement_row)
values (')')
/* could insert a 'go' row here if desired */

/* now evaluate sysindexes and set up the create index statements; use sp_helpindex as template */

/* select results */
select * from #create_table
drop table #create_table
return
go

BOL,

John J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net

 

[babeo]

Thanks Craig,

That's good help. I'l try it out now.
Carol