none
Como automatizar um "Generate Scripts" RRS feed

  • Pergunta

  • Boa Tarde,

               estou com uma demanda de automatizar scritps de DML e DDL semanalmente. Gostaria de saber se existe alguma forma pronta no SQL Server 2008? Caso não eu consigo gerar um T-SQL do Generate Scripts do mesmo? 

    Att.


    Igor Auler

    terça-feira, 7 de fevereiro de 2012 14:34

Todas as Respostas

  • Boa Tarde,

    Seguem alguns links interessantes:

    Use PowerShell to Script SQL Database Objects
    http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/04/use-powershell-to-script-sql-database-objects.aspx

    SQL SERVER – 2005 – Generate Script with Data from Database – Database Publishing Wizard
    http://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/

    Procedure to script your data (to generate INSERT statements from the existing data)
    http://vyaskn.tripod.com/code.htm#inserts

    No seu caso, sugiro combinar os dois primeiros para poder fazer tudo em um mesmo PROMPT (ou BATCH)

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos:http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível

    terça-feira, 7 de fevereiro de 2012 17:32
  • Gustavo,

               obrigado pelos links, estava olhando agora e precisa instalar algumas coisas e como este servidor que eu preciso gerar esse job de generate scripts DDL e DML não é meu, eu não consigo instalar nada nele. Por isto eu criei procedures que fizesse isto.

    - Criei uma procedure que gera o insert de todos os dados da base (caso passe NULL como parametro) ou cria os inserts de apenas uma tabela(passando o nome da tabela como parametro): (está procedure ficou muito boa, por coloco ela abaixo para compartilhar com vocês)

    DROP PROCEDURE [dbo].[GerAScript]
    /****** Object:  StoredProcedure [dbo].[GerAScript]    Script Date: 02/08/2012 17:43:27 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
      
    CREATE PROCEDURE [dbo].[GerAScript] (  
    	@p_tbl_verifica  VARCHAR(50)  
    )  
    /************************************************************************  
    *  
    *   Autor: Igor Auler  
    *  
    *   Parametro: Nome da tabela, ou parte do nome como filtro 
    *	(Cuidado para que esta parte do nome não seja comum para mais de uma tabela ou gerará script para todas estas tabelas),  
    *	ou NULL para todas as tabelas
    *  
    *************************************************************************/  
    AS  
    BEGIN
    	DECLARE @v_tbl_aux  VARCHAR(50)  
    	DECLARE @v_tbl		VARCHAR(50)  
    	DECLARE @v_campo	VARCHAR(50)  
    	DECLARE @v_tipo		CHAR(1)  
    	DECLARE @v_sql1		VARCHAR(4000)  
    	DECLARE @v_sql2		VARCHAR(4000)
    	
    	CREATE TABLE #Tab (
    		nome_objeto	VARCHAR(256),
    		tipo_objeto INT,
    		QInsert VARCHAR(MAX)
    	)
    	
    	INSERT INTO #tab
    		SELECT 'AAAAAA',0 ,'EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"'
    		
    	DECLARE Cur_Tabs_Campos CURSOR FOR
    	
    		SELECT  
    			T.name,  
    			C.name,  
    			CASE C.type  
    				WHEN 39 THEN 'C'  
    				WHEN 47 THEN 'C'  
    				WHEN 61 THEN 'D'  
    				WHEN 58 THEN 'D'  
    				WHEN 111 THEN 'D'  
    				ELSE 'N'  
    			END  
    		FROM  
    			sysobjects T 
    			INNER JOIN syscolumns C
    			ON T.id = C.id  
    		WHERE   
    			T.NAME <> 'sysdiagrams' AND -- INCLUIDO PARA NÃO GERAR SCRIPT DO SYSDIAGRAMS
    			T.type = 'u' AND  
    			(CASE  
    				WHEN @p_tbl_verifica IS NULL THEN 1  
    				WHEN T.name = @p_tbl_verifica THEN 1  
    				WHEN T.name LIKE  '%' + @p_tbl_verifica + '%'  THEN 1   
    				ELSE 0  
    			END) = 1  
    		ORDER BY 
    			1, 
    			2    
      
    		
    		OPEN Cur_Tabs_Campos  
    		FETCH NEXT FROM Cur_Tabs_Campos  
    		INTO @v_tbl, @v_campo, @v_tipo  
    		
    		WHILE @@FETCH_STATUS = 0  
    		
    		BEGIN  
    			
    			SET @v_sql1 = ''  
    			SET @v_sql2 = ''  
    			SET @v_tbl_aux = @v_tbl  
    			WHILE @@FETCH_STATUS = 0 AND @v_tbl_aux = @v_tbl  
    			
    			BEGIN  
       
    				IF @v_sql1 <> ''
    					SET @v_sql1 = @v_sql1 + ', '  
    				
    				SET @v_sql1 = @v_sql1 + @v_campo  
    				
    				IF @v_sql2 <> ''  
    					SET @v_sql2 = @v_sql2 + ' + '  
    				
    				IF @v_tipo = 'N'  
    					SET @v_sql2 = @v_sql2 + 'REPLACE(ISNULL ( convert ( VARCHAR, ' + @v_campo + '), ''NULL''),CHAR(39),CHAR(39)+CHAR(39)) + '', ''' -- CHAR(39) = ' / substitui na string quando houver ' por '' 
    
    				ELSE IF @v_tipo = 'D'  
    					SET @v_sql2 = @v_sql2 + ''''''''' + ' + 'REPLACE(ISNULL ( convert ( VARCHAR, ' + @v_campo + '), ''NULL''),CHAR(39),CHAR(39)+CHAR(39)) + '''''', '''  
    
    				ELSE
    					SET @v_sql2 = @v_sql2 + ''''''''' + REPLACE(ISNULL ( ' + @v_campo + ', ''NULL''),CHAR(39),CHAR(39)+CHAR(39)) + '''''', '''  
    					
    				FETCH NEXT FROM Cur_Tabs_Campos  
    				INTO @v_tbl, @v_campo, @v_tipo
    				
    			END  
    	
    				IF(SELECT
    					1
    				FROM 
    					SYS.OBJECTS O
    					INNER JOIN SYS.COLUMNS C ON C.OBJECT_ID = O.OBJECT_ID
    				WHERE
    					C.is_identity = 1
    					AND O.NAME = @v_tbl_aux) = 1
    				
    					BEGIN
    						
    						INSERT INTO #tab
    							SELECT '' + @v_tbl_aux + '' ,1 ,'SET IDENTITY_INSERT ' + @v_tbl_aux + ' ON'
    						
    						SET @v_sql1 = 'insert INTO #Tab SELECT ''' + @v_tbl_aux + ''' ,2 ,''insert INTO ' + @v_tbl_aux + ' ( ' + @v_sql1 + ') values ( '' + '  
    						SET @v_sql2 = substring ( @v_sql2, 1, len ( @v_sql2) - 3) + ')'' FROM ' + @v_tbl_aux 
    						
    						EXEC ( @v_sql1 + @v_sql2)
    	
    						INSERT INTO #tab
    							SELECT '' + @v_tbl_aux + '' ,3 ,'SET IDENTITY_INSERT ' + @v_tbl_aux + ' OFF'
    					END
    				
    				ELSE
    
    					BEGIN
    
    						SET @v_sql1 = 'insert INTO #Tab SELECT ''' + @v_tbl_aux + ''' ,2 ,''insert INTO ' + @v_tbl_aux + ' ( ' + @v_sql1 + ') values ( '' + '  
    						SET @v_sql2 = substring ( @v_sql2, 1, len ( @v_sql2) - 3) + ')'' FROM ' + @v_tbl_aux  
    					
    						EXEC ( @v_sql1 + @v_sql2)
    
    					END 
    	
    		END  
    	
    		CLOSE Cur_Tabs_Campos  
    		DEALLOCATE Cur_Tabs_Campos  
    		
    		INSERT INTO #tab
    			SELECT 'ZZZZZZ' ,4 ,'EXEC sp_msforeachtable "ALTER TABLE ? CHECK CONSTRAINT all"'
    			
    		SELECT
    			REPLACE ( QInsert, '''NULL''', 'NULL') 
    		FROM
    			#Tab
    		ORDER BY
    			nome_objeto,
    			tipo_objeto
    END

    Depois criei um job onde executa esta procedure e joga a saida dela para um arquivo através do xp_cmdshell :

    USE [ServiceControl]
    GO
    
    declare 	@cmd		VARCHAR(1000)
    declare 	@query 		VARCHAR(1000) 
    declare		@arquivo	VARCHAR(200)
    declare 	@caminho	VARCHAR(200)
    declare 	@data		VARCHAR(100)
    
    CREATE TABLE ##TMP1 -- Cria tabela temporaria
    (
    	id INT identity (1,1),
    	QInsert VARCHAR(MAX)
    )
    
    INSERT INTO ##TMP1 -- insere na tabela temporaria os scripts de insert
    	EXEC dbo.GeraScript null
    
    SET 
    	@data = convert(VARCHAR,getdate(),112) -- coloca a data corrente no formato AAAAMMDD
    
    SELECT 
    	@caminho = 'C:\Sql2005\Scripts\' -- Seta o caminho em que o arquivo será criado
    
    SELECT 
    	@arquivo = coalesce (@caminho,'C:\Sql2005\Scripts\') -- Coloca o arquivo com o nome especificado acima, caso este esteja null é utilizdo o diretorio c:SET 
    
    SELECT	
    	@arquivo = @arquivo + 'DML_' + @data + '.sql' -- Seta o nome do arquivo neste caso DML_AAAAMMDD.sql
    
    SELECT 
    	@query = '"SELECT QInsert FROM ServiceControl..##TMP1 ORDER BY id"' -- seta a query que ira retornar a saida para oa rquivo
    
    SELECT 
    	@cmd = 'bcp ' + @query + ' queryout ' + @arquivo + ' -S' + @@Servername + ' -C"ANSI" -T -c -t";"'
    
    EXEC xp_cmdshell @cmd
    
    DROP TABLE ##TMP1

    Fiz a mesma coisa para gerar o DLL, porém eu gerei o DDL das PROCEDURES, TRIGGERS, FUNCTIONS E VIEWS através da sp_helptext, mas para minha surpresa esta procedure não funciona com TABELAS :(. Procurei na internet e achei um procedure que fizesse o mesmo da sp_helptext, só que para tabelas, porem não funcionou muito bem.

    Segue abaixo os passos que eu utilizei para gerar o DDL das PROCEDURES, TRIGGERS, FUNCTIONS, VIEWS E TABLES. (Porem a de tables não está 100% na parte dos indices)

    1º criei uma procedure similar a sp_helptext para tabelas(pega na internet):

    Create  Proc sp_helpTable
    (
                @TableName Nvarchar(2000)
    )
    as
    Create Table #Text
    (
                Source NVarchar(4000)
    )
     
    Insert Into #Text(Source) Values ('Create Table ' + @TableName + '(');
     
    Insert Into #Text(Source)
    Select
     '[' + C.Name + '] ' +  Ty.name + Case When C.Scale Is NULL Then '(' + Cast(C.Length as Varchar) + ') ' Else '' End +
                Case When C.IsNullable =0 And C.Colstat & 1 <> 1 Then ' NULL ' Else ' NOT NULL ' End 
                + Case When C.Colstat & 1 = 1 Then ' Identity(' +  Cast(ident_seed(T.name) as varchar) + ',' + Cast(ident_incr(T.name) as Varchar) + ') '  Else '' End
                + Isnull(' Constraint ' + ChkCon.Name + ' Check ' + comments.Text ,'')
                + Isnull(' Default ' + defcomments.text ,'') + ','
    From
     Sysobjects T
      Join Syscolumns C on T.id = C.Id
      Join systypes Ty On C.xtype = Ty.xType And Ty.Name <> 'SysName'
      Left Outer Join sysobjects ChkCon On ChkCon.parent_obj = T.Id
                            And ChkCon.xtype= 'C' And ChkCon.Info = C.Colorder
                Left Outer Join syscomments comments ON Comments.id = ChkCon.id And Comments.colid =1
      Left Outer Join sysobjects def On def.parent_obj = T.Id
                            And def.xtype= 'D' And def.Info = C.Colorder
                Left Outer Join syscomments defcomments ON defcomments.id = def.id 
     
    Where
     T.Type='U'
     And T.Name=@TableName
    Order By
     T.Name,
     C.Colorder
     
     
    Insert Into #Text(Source)
    Select
                'Constraint [' + ind.name + '] ' + case when xtype='PK' Then ' Primary Key ' Else ' Unique ' End  + Case when ind.status & 16=16 Then ' clustered ' Else ' nonclustered' End  +  '(' +  dbo.GetAllIndexedColumns(@TableName, 2)  + '),'
    From
                sysindexes ind Join sysobjects tbl On tbl.parent_obj = object_id(@TableName)
                            and ind.name = object_name(tbl.id)
                            and xtype in ('PK', 'UQ')
     
    Insert Into #Text(Source)
    select
                'Constraint [' + tbl.name + '] FOREIGN KEY ([' + col_name(fk.fkeyid, fk.fkey) + ']) REFERENCES [' + 
                object_name(fk.rkeyid) + ']([' + col_name(fk.rkeyid, fk.rkey) + ']),'
    from    
                sysforeignkeys fk Join sysobjects tbl On tbl.parent_obj = object_id(@TableName)
                                        and fk.constid = tbl.id
                                        and xtype in ('F')
     
    Declare @Source as Nvarchar(4000);
    Select @Source = Source From #Text;
    Update #Text
    Set
                Source = Substring(Source,1,Len(Source)-1)
    Where
                Source = @Source;
     
    Insert Into #Text(Source) values (')');
     
    Select Source From #Text

    2º - precisei criar a function GetAllIndexedColumns utilizada na procedure acima para gerar os indices:

    Create Function dbo.GetAllIndexedColumns(@ObjectName nvarchar(1000), @indexid int)
    Returns NVarchar(4000)
    as
    Begin
     Declare @IndexedCols as NVarchar(4000);
     Declare @I as Int;
     Select @I = 1;
     Select @IndexedCols = index_col(@ObjectName, @indexid, @I) + '  ' + Case When indexkey_property(object_id(@ObjectName),@indexid, @i, 'isdescending') = 1 Then ' Desc ' Else '' End
     Select @I = @I +1;
     While index_col(@ObjectName, @indexid, @I) is not null
     Begin
      Select @IndexedCols = @IndexedCols + ',' + index_col(@ObjectName, @indexid, @I) + '  ' + Case When indexkey_property(object_id(@ObjectName),@indexid, @i, 'isdescending') = 1 Then ' Desc ' Else '' End
      Select @I = @I +1;
     End
     return @IndexedCols;
    End

    E por ultimo criei a procedure que gera todos os DDLS:

    CREATE PROCEDURE GeraScriptDDL
    AS
    BEGIN
      SET NOCOUNT ON
      CREATE TABLE #MyObjectHierarchy 
       (
        HID int identity(1,1) not null primary key,
        ObjectId int,
        TYPE int,OBJECTTYPE AS CASE 
                                 WHEN TYPE =  1 THEN 'FUNCTION' 
                                 WHEN TYPE =  4 THEN 'VIEW' 
                                 WHEN TYPE =  8 THEN 'TABLE' 
                                 WHEN TYPE = 16 THEN 'PROCEDURE'
                                 WHEN TYPE = 128 THEN 'RULE'
                                 WHEN TYPE = 256 THEN 'TRIGGER'
                                 ELSE ''
                               END,
       ONAME varchar(255), 
       OOWNER varchar(255), 
       SEQ int
       )
       --our results table
      CREATE TABLE #Results(ResultsID int identity(1,1) not null,ResultsText varchar(max) )
      --our list of objects in dependancy order
      INSERT #MyObjectHierarchy (TYPE,ONAME,OOWNER,SEQ)
        EXEC sp_msdependencies @intrans = 1 
    
     Update #MyObjectHierarchy SET ObjectId = object_id(OOWNER + '.' + ONAME)
      --synonyns are object type 1 Function?!?!...gotta remove them
      DELETE FROM #MyObjectHierarchy WHERE objectid in(
        SELECT [object_id] FROM sys.synonyms UNION ALL
        SELECT [object_id] FROM master.sys.synonyms)
        --custom requirement: only objects starting with KLL
      --DELETE FROM #MyObjectHierarchy WHERE  LEFT(ONAME,3) <> 'KLL' 
      DECLARE
        @schemaname     varchar(255),
        @objname        varchar(255),
        @objecttype     varchar(20),
        @FullObjectName varchar(510)
    
      DECLARE cur1 CURSOR FOR 
        SELECT OOWNER,ONAME,OBJECTTYPE FROM #MyObjectHierarchy ORDER BY HID
      OPEN cur1
      FETCH NEXT FROM cur1 INTO @schemaname,@objname,@objecttype
      WHILE @@fetch_status <> -1
    	BEGIN
           SET @FullObjectName = QUOTENAME(@schemaname) + '.' + QUOTENAME(@objname)
                  PRINT @FullObjectName
           IF @objecttype IN( 'VIEW','FUNCTION','PROCEDURE','TRIGGER')
             BEGIN
               INSERT INTO #Results(ResultsText)
                 EXEC sp_helptext @FullObjectName
            --we need a batch seperator:
            INSERT INTO #Results(ResultsText)
            SELECT 'GO'
    
              END
              
           IF @objecttype IN( 'TABLE')
             BEGIN
               INSERT INTO #Results(ResultsText)
                 EXEC sp_helpTable @objname		-- é preciso criar a procedure sp_helptable / e tambem a function GetAllIndexedColumns
            --we need a batch seperator:
            INSERT INTO #Results(ResultsText)
            SELECT 'GO'
    
              END
                        
    	   FETCH NEXT FROM cur1 INTO @schemaname,@objname,@objecttype
    	 END
        CLOSE cur1
        DEALLOCATE cur1
      SELECT ResultsText FROM #Results ORDER BY ResultsID
    END
    GO

    Gostaria de saber se alguem sabe ou tem alguma procedure que gera o DDL das tabelas e dos indices.

    Att.


    Igor Auler


    • Editado Igor Auler quinta-feira, 9 de fevereiro de 2012 17:44 Faltou a parte do DDL
    quinta-feira, 9 de fevereiro de 2012 17:34