Solved

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

Posted on 1998-12-04
12
813 Views
Last Modified: 2011-10-03
Is there a stored procedure available for sql server 6.5 to generate scripts for databases, tables, indexes views ?
Please help.
0
Comment
Question by:abgh079
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 7

Expert Comment

by:tchalkov
ID: 1092076
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
 

Author Comment

by:abgh079
ID: 1092077
i could not find the stored procedure mentioned
0
 

Author Comment

by:abgh079
ID: 1092078
i could not find the stored procedure mentioned
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:aliciaam
ID: 1092079
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
 

Author Comment

by:abgh079
ID: 1092080
the above stored procedure only generates the script for the database and not the dependent objects like the tables,views etc
0
 
LVL 2

Expert Comment

by:aliciaam
ID: 1092081
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
 
LVL 2

Accepted Solution

by:
tschill120198 earned 100 total points
ID: 1092082
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
 

Author Comment

by:abgh079
ID: 1092083
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
 
LVL 2

Expert Comment

by:tschill120198
ID: 1092084
What do you mean by "I don't know which have either been added or dropped"?  
0
 

Author Comment

by:abgh079
ID: 1092085
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
 
LVL 2

Expert Comment

by:tschill120198
ID: 1092086
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
 

Author Comment

by:abgh079
ID: 1092087
thanks tcshill!!!! you were a great help to my problem. your solution did the trick.
thanks
abgh079
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question