We help IT Professionals succeed at work.

Defncopy Question

CChoks
CChoks asked
on
I am using defncopy to copy the table definition out of a database but all i seem to be getting is an almost empty file that says /* ### DEFNCOPY: END OF DEFINITION */. i followed the parameters exactly but i cant seem to get the definition out...anyone know why?
Comment
Watch Question

this is a standard thing and it would alway come when you run defncopy.

the best way to remove it could be to run a shell command on the ouptut file ..

cat Outfile.txt | grep -v "DEFNCOPY: END OF DEFINITION" > tmp
mv tmp outfile.txt

thanks.
Commented:
You have missed the working of DEFNCOPY.It can only be used to copy definitions for speccified   views,   rules,   defaults,   triggers, or   procedures from  a database to an operating system file.

You cannot use to copy a table definition out of a database with DEFNCOPY.

You have to use Sybase Central or  sql script to get the definiton of a table.

Hope this solves your problem.
The following procedure could be useful in determining the structure of a table

Syntax: exec sp_table "tableName"


Here is the text of the sp.

 create  procedure       sp_table
        @tabname        varchar(30)
as
        declare @max tinyint,
                @j tinyint,
                @msg varchar(255)

        select @max=max(colid)
        from syscolumns
        where id = object_id(@tabname)

        if (@max is null)
        begin
                raiserror 20001 "No such table"
                return 1
        end

        select @msg = "create   table   " + @tabname + "
("
        print @msg

        select @j = 1
        while (@j <= @max)
        begin
                if not exists
                (
                select *
                from    syscolumns c,
                        systypes t
                where c.id = object_id(@tabname)
                and c.usertype *= t.usertype
                and c.colid=@j
                )
                begin
                        select @j=@j+1
                        continue
                end

                select @msg =  "        " + c.name +  " " + t.name
                + substring(" (" + convert(varchar(3), c.length) + ")", 1,
                1 + 6 * (charindex(t.name, "varchar/varbinary")))
                + substring(" null", 1, 1 + 5 *
                (charindex(convert(varchar(3),c.status), "8")))
                from    syscolumns c,
                        systypes t
                where   c.id = object_id(@tabname)
                and     c.usertype *= t.usertype
                and     c.colid=@j

                if @j != @max
                        select @msg = @msg + ","

                print @msg
                select @j=@j+1
        end
        print ")"