Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 1998-12-04
12
Medium Priority
?
830 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

715 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