stored procedure to generate scripts of tables (sql server6.5)

Is there a stored procedure available for sql server 6.5 to generate scripts for databases, tables, indexes views ?
Please help.
abgh079Asked:
Who is Participating?
 
tschill120198Connect With a Mentor Commented:
Here are the sprocs needed for the original answer; compile them and then run the script that tchalkov included.

----------------------------------------
-- dbo.sp_hexadecimal
----------------------------------------

if object_id('dbo.sp_hexadecimal') is not null begin
    print 'Dropping procedure sp_hexadecimal'
    drop procedure dbo.sp_hexadecimal
end
go

print 'Creating procedure sp_hexadecimal'
go

create procedure dbo.sp_hexadecimal
    @binvalue varbinary(255),
    @hexvalue varchar(255) OUTPUT
as
declare @charvalue varchar(255),
        @i int,
        @length int,
        @hexstring char(16)

select  @charvalue = '0x',
        @i = 1,
        @length = DATALENGTH(@binvalue),
        @hexstring = '0123456789abcdef'

while (@i <= @length)
begin
    declare @tempint int, @firstint int, @secondint int

    select @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
    select @firstint = FLOOR(@tempint/16)
    select @secondint = @tempint - (@firstint*16)
    select @charvalue = @charvalue +
        SUBSTRING(@hexstring, @firstint+1, 1) +
        SUBSTRING(@hexstring, @secondint+1, 1)
    select @i = @i + 1
end
select @hexvalue = @charvalue
go
 
----------------------------------------
-- dbo.sp_displayoaerrorinfo
----------------------------------------

if object_id('dbo.sp_displayoaerrorinfo') is not null begin
    print 'Dropping procedure sp_displayoaerrorinfo'
    drop procedure dbo.sp_displayoaerrorinfo
end
go

print 'Creating procedure sp_displayoaerrorinfo'
go

create procedure dbo.sp_displayoaerrorinfo
    @object int,
    @hresult int
as
declare @output varchar(255),
        @hrhex char(10),
        @hr int,
        @source varchar(255),
        @description varchar(255)

print   'OLE Automation Error Information'
exec sp_hexadecimal @hresult, @hrhex OUT

select  @output = '  HResult: ' + @hrhex
print   @output
exec @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
if @hr = 0
begin
    select @output = '  Source: ' + @source
    PRINT @output
    select @output = '  Description: ' + @description
    PRINT @output
end
else
begin
    print "  sp_OAGetErrorInfo failed."
    return
end
 
 

0
 
tchalkovCommented:
There is no such procedure but there is a way this can be done using Distributed Management Objects from TSQL. Here is an example how this can be done. I will show how to script a table. For other objects the idea is the same. There is a stored procedure I am using - sp_displayoaerrorinfo which can be found in SQL Server Books Online
You can change tables(…) with any of the following:
Defaults, Groups, Rules, StoredProcedures, Tables, UserDefinedDatatypes, Users, Views

Sample:
declare @obj1 int, @res int,
      @servername varchar(255),
      @tempstr varchar(255)
select @servername='ntserver'
 
exec @res=sp_OACreate 'sqlole.sqlserver',@obj1 output
if @res<>0 exec sp_displayoaerrorinfo @obj1,@res

exec @res=sp_OAMethod @obj1,'Connect',NULL, @servername, 'yourusername','yourpassword'
if @res<>0 exec sp_displayoaerrorinfo @obj1,@res
select @tempstr='databases("pubs").tables("authors").Script'
exec @res=sp_OAMethod @obj1,@tempstr,NULL, 422143, 'c:\temp\test.sql'
if @res<>0 exec sp_displayoaerrorinfo @obj1,@res
exec sp_OADestroy @obj1

This will make a file which contains the script of table authors and all its indexes, defaults, constraints. There is also a way to show the script on the screen but for some reason if it is longer than a few hundred chars it is truncated.
0
 
abgh079Author Commented:
i could not find the stored procedure mentioned
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
abgh079Author Commented:
i could not find the stored procedure mentioned
0
 
aliciaamCommented:
there is one sp_help_revdatabase

it will give you the whole "reversed engineering" for that database

sp_help_revdatabase [DBNamePattern]

where

DBNamePattern

Specifies a database name pattern. The name pattern must adhere to the LIKE operator standards for string arguments. The default is '%' (all databases will be affected).
For more information about the LIKE operator, see Wildcard Characters in the Microsoft SQL Server Transact-SQL Reference.

Remarks

The purpose of the sp_help_revdatabase stored procedure is to ease the task of building a database by ensuring the lpage (logical page) and segmap (segment map) structures are compatible with an existing database.

0
 
abgh079Author Commented:
the above stored procedure only generates the script for the database and not the dependent objects like the tables,views etc
0
 
aliciaamCommented:
You should buy a tool such as DBArtisan or Erwin to do reverse engineering. Or just create the script with SQL Enterprise Manager. Good Luck
0
 
abgh079Author Commented:
thanks tschill , your solution has helped me a lot. just one further question though. i wan to script all the tables and indexes  in one database, I don't know which have either been added or dropped. How can I insert variables to solve this problem
0
 
tschill120198Commented:
What do you mean by "I don't know which have either been added or dropped"?  
0
 
abgh079Author Commented:
tscill , I meant that during maintainance tables are either added or dropped. if you were to hardcode the names of the tables, and this scriptjob would run after you have dropped a certain table , your job will crash , because it cannot find that table. if you were to add a table during maintainance and you have forgotten to add its name to the script job, as a result your table will not be scripted.

i hope i explained myself clearer.
0
 
tschill120198Commented:
One way would be to cursor through sysobjects, scripting out each table.  The example below uses sysobjects and works on 6.5, but I don't know if it will on SQL 7.  

    declare @obj1       int,
            @res        int,
            @servername varchar(255),
            @dbname     varchar(30),
            @tablename  varchar(30),
            @filename   varchar(255),
            @tempstr    varchar(255)
   
    select  @servername = '<server>',
            @dbname     = db_name()
     
    exec @res=sp_OACreate 'sqlole.sqlserver', @obj1 output
    if @res <> 0 exec sp_displayoaerrorinfo @obj1, @res
   
    exec @res=sp_OAMethod @obj1, 'Connect', null, @servername, '<user>', '<pwd>'
    if @res <> 0 exec sp_displayoaerrorinfo @obj1, @res
   
    declare cTables cursor for
        select name from sysobjects where type = 'u'
    open cTables
    while (1=1)
    begin
        fetch next from cTables into @Tablename
        if @@fetch_status = -1 break
        if @@fetch_status = -2 continue
        if @@fetch_status <> 0 break
   
        select @tempstr = 'databases("' + @dbname + '").tables("' + @TableName + '").Script'
        select @Filename = 'c:\temp\' + @Tablename + '.sql'
        select 'Scripting table ' + @Tablename
   
        exec @res=sp_OAMethod @obj1, @tempstr, null, 422143, @FileName
        if @res <> 0 exec sp_displayoaerrorinfo @obj1, @res
    end
   
    close cTables
    deallocate cTables
   
    exec sp_OADestroy @obj1

0
 
abgh079Author Commented:
thanks tcshill!!!! you were a great help to my problem. your solution did the trick.
thanks
abgh079
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.