Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 1998-12-04
12
Medium Priority
?
839 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 300 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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 part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

971 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