Solved

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

Posted on 1998-12-04
12
790 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now