Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
produce codes 1
- Thread starter babeo
- Start date
dickiebird
Programmer
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
eg sp_help tablename
sp_help viewname
For database : sp_helpdb
HTH ;-) Dickie Bird
db@dickiebird.freeserve.co.uk
- Thread starter
- #3
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
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
Programmer
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
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
- Thread starter
- #5
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
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
-
1
- #6
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 ("CREATE TABLE " + @tablename + "("
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
nsjmcraig@netscape.net
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 ("CREATE TABLE " + @tablename + "("
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
nsjmcraig@netscape.net
- Thread starter
- #7
- Status