Solved

Automating generation of SQLScript Schema

Posted on 2002-07-05
10
326 Views
Last Modified: 2008-02-01
I have a database schema that is constantly changing due to progressive design decisions ;) (Could also be considered as lack of design).  
I have constructed a database maintenance plan, however this just backs up the data in the table.  Which is useless for disaster recovery processes without the latest SQLScript Schema having been saved out prior to it.

Could someone please tell me by example or reference how-to automate the generation of SQL Scripts for all of databases and tables currently active on SQLServer.  

TIA.



0
Comment
Question by:cwgues
  • 5
  • 4
10 Comments
 
LVL 6

Expert Comment

by:curtis591
ID: 7132047
This is the setup of stored procedure that I use. It saves the scripts with the date in the filename on the server and I have a scheduled job that executes this every night.

CREATE procedure sp_script_sql_server
as
begin
    set nocount on
    declare @obj1       int,         @res        int,             @tempstr    varchar(255),             @msg        varchar(255),
            @tablename  varchar(50),             @filename   varchar(255),             @dbname     varchar(50),
            @servername varchar(50),            @ViewName   varchar(50),            @SPName   varchar(50),
            @loginname   varchar(50),            @UserName   varchar(50),            @DefaultName   varchar(50)

   
    if @servername is null select @servername = @@servername
    if @dbname is null select @dbname = db_name()

    exec @res=sp_OACreate 'sqldmo.sqlserver', @obj1 output
    if @res <> 0 exec sp_displayoaerrorinfo @obj1, @res
 
    exec @res=sp_OAMethod @obj1, 'Connect', null, @servername
    if @res <> 0 exec sp_displayoaerrorinfo @obj1, @res

    declare clogins cursor for
         select name from master..syslogins
    open clogins
    while (1=1)
    begin
        fetch next from clogins into @loginname
        if @@fetch_status = -1 break
        if @@fetch_status = -2 continue
        if @@fetch_status <> 0 break

       select @Filename = 'd:\sql_scripts\'+right('0'+ltrim(str(DATEPART(m, getdate()))),2)+'-'+right('0'+ltrim(str(DATEPART(d,getdate()))),2)+'-'+ltrim(str(DATEPART(yy, getdate()))) + 'logins.sql'
       select @tempstr = 'logins("' + @loginname  + '").Script'
       select 'Scripting Login ' + @loginname
       exec @res=sp_OAMethod @obj1, @tempstr, null, 135461, @FileName
    end
    close clogins
    deallocate clogins

    declare cdatabases cursor for
      select name from master..sysdatabases
    open cdatabases
    while (1=1)
    begin
      fetch next from cdatabases into @dbname
      if @@fetch_status = -1 break
      if @@fetch_status = -2 continue
      if @@fetch_status <> 0 break
      select @Filename = 'd:\sql_scripts\'+right('0'+ltrim(str(DATEPART(m, getdate()))),2)+'-'+right('0'+ltrim(str(DATEPART(d,getdate()))),2)+'-'+ltrim(str(DATEPART(yy, getdate()))) +@dbname+ '.sql'


      exec ('declare cdefaults cursor for select name from ' + @dbname + '..sysobjects where type = "D"')
      open cdefaults
      while (1=1)
      begin
         fetch next from cdefaults into @DefaultName
          if @@fetch_status = -1 break
          if @@fetch_status = -2 continue
          if @@fetch_status <> 0 break
          select @tempstr = 'databases("' + @dbname + '").defaults("' + @DefaultName + '").Script'
          exec @res=sp_OAMethod @obj1, @tempstr, null, 422399, @FileName
          if @res <> 0 exec sp_displayoaerrorinfo @obj1, @res
      end
      close cdefaults
      deallocate cdefaults


      exec ('declare cusers cursor for select name from ' + @dbname + '..sysusers where status <> 0')
      open cusers
      while (1=1)
      begin
         fetch next from cusers into @UserName
          if @@fetch_status = -1 break
          if @@fetch_status = -2 continue
          if @@fetch_status <> 0 break

          select @tempstr = 'databases("' + @dbname + '").users("' + @UserName + '").Script'
          select 'Scripting User ' + @UserName
          exec @res=sp_OAMethod @obj1, @tempstr, null, 422399, @FileName
          if @res <> 0 exec sp_displayoaerrorinfo @obj1, @res
      end
      close cusers
      deallocate cusers

      exec ('declare ctables cursor for select name from ' + @dbname + '..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 'Scripting Database ' + @dbname + ' - ' + @TableName
          exec @res=sp_OAMethod @obj1, @tempstr, null, 422399, @FileName

      end
      close ctables
      deallocate ctables

      exec ('declare cviews cursor for select name from ' + @dbname + '..sysobjects WHERE type = "V" AND category = 0')
      open cviews
      while (1=1)
      begin
        fetch next from cviews into @ViewName
          if @@fetch_status = -1 break
          if @@fetch_status = -2 continue
          if @@fetch_status <> 0 break

          select @tempstr = 'databases("' + @dbname + '").views("' + @ViewName + '").Script'
          select 'Scripting View ' + @dbname + ' - ' + @ViewName
          exec @res=sp_OAMethod @obj1, @tempstr, null, 422399, @FileName
      end
      close cviews
      deallocate cviews

      exec ('declare cstsp cursor for select name from ' + @dbname + '..sysobjects WHERE type = "P" AND category = 0')
      open cstsp
      while (1=1)
      begin
        fetch next from cstsp into @SPName
          if @@fetch_status = -1 break
          if @@fetch_status = -2 continue
          if @@fetch_status <> 0 break

          select @tempstr = 'databases("' + @dbname + '").storedprocedures("' + @SPName + '").Script'
          select 'Scripting Stored Procedure ' + @dbname + ' - ' + @SPName
          exec @res=sp_OAMethod @obj1, @tempstr, null, 422399, @FileName
      end
      close cstsp
      deallocate cstsp
    end
    close cdatabases
    deallocate cdatabases
    exec sp_OADestroy @obj1
end

GO



create procedure dbo.sp_hexadecimal (
    @binvalue varbinary(255),
    @hexvalue varchar(255) OUTPUT
)
AS
set nocount on
DECLARE @charvalue varchar(255)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH(@binvalue)
SELECT @hexstring = '0123456789abcdef'
WHILE (@i <= @length)
BEGIN
    DECLARE @tempint int
    DECLARE @firstint int
    DECLARE @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



create procedure dbo.sp_displayoaerrorinfo (
    @object int,
    @hresult int
)
AS

set nocount on

DECLARE @output varchar(255)
DECLARE @hrhex char(10)
DECLARE @hr int
DECLARE @source varchar(255)
DECLARE @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
 
LVL 1

Author Comment

by:cwgues
ID: 7132111
Thanks for the prompt reply Curtis,
       However, I'm quite new to the SQLScript syntax.  Without comments I'm a little dumbfounded as to what it does.  Any chance of a simplified commented version or at least some general comments around the paragraphs of scripts.  

Is this something that you have generated yourself? Or have you used a wizard?  Also if you can remember, do you know where you found the information on what to do?

TIA.
0
 
LVL 6

Accepted Solution

by:
curtis591 earned 150 total points
ID: 7132228
I generated it myself with some help form the books on line and the microsoft site.  They call it sqldmo and all it is doing is calling com objects of the sql server.
First part generates the file name and then what it does it makes a cursor for of the databases and then it loops through each database and retrieves the tables, stored procedures, views and it runs the script method from the com object.
exec @res=sp_OAMethod @obj1, @tempstr, null, 422399, @FileName
the 422399 tell it how to script the object.  They are all listed in the BOL under the script method.
0
 
LVL 6

Expert Comment

by:curtis591
ID: 7132233
At the start it scripts all the logins for the server. Then it starts with the databases
0
 
LVL 1

Author Comment

by:cwgues
ID: 7132385
Thanks Curtis,  
   I am beginning to understand it now.  It just looked mind-boggling for a moment.  Do you know if it is possible to trigger a script such as this on an event fired by a change in the database schema?  This would then offer me peace of mind that I can update the schema at any time and have an up-to-date backup.

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 6

Expert Comment

by:curtis591
ID: 7132403
I have actually looked for the same sort of trigger for a different reason but I haven't found a way to do it.  If you find one let me know.  
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 7132407
Whenever you change the schema you should take a backup - both before and afterwards.

See www.nigelrivett.com for a procedure for implementing releases and always maintaining a copy of the schema in sourcesafe.
I am at present expanding this for publication.
0
 
LVL 1

Author Comment

by:cwgues
ID: 7132432
Thanks Curtis,  
   I am beginning to understand it now.  It just looked mind-boggling for a moment.  Do you know if it is possible to trigger a script such as this on an event fired by a change in the database schema?  This would then offer me peace of mind that I can update the schema at any time and have an up-to-date backup.

0
 
LVL 1

Author Comment

by:cwgues
ID: 7132443
Cheers, I will let you know Curtis..

Nigel, I hope to see you completed article.  I enjoy reading about how other people develop especially when they seem to care about what they do.  

BTW: Don't press refresh on these pages after just adding a comment..

0
 
LVL 6

Expert Comment

by:curtis591
ID: 7132454
There is a reload question button at the top right hand side of the page to stop that from happening.  I have posted the question about tracking changes, I will see if we get an answer.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

746 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

13 Experts available now in Live!

Get 1:1 Help Now