Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    Mumbai
    Posts
    19

    Unanswered: How To Generate SQL Script

    I want to generate the SQL Script of Stored Procedurein Query Analyser.
    What is the procedure for that.
    I can do it through Enterprise Manager by right click and Generate SQL Script but I want to do it programatically.Can anyone tel m ehow to do that ???

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Ummm......

    Are you looking for this

    Code:
    sp_helptext
    Prints the text of a rule, a default, or an unencrypted stored procedure, user-defined function, trigger, or view.
    Look up sp_helptext in the Holy Book (SQL server Books online)
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Dec 2002
    Location
    Mumbai
    Posts
    19
    This don't solve my purpose..I already tested it.The scrupt which gets generated,I want to use it directly to create exactly similar SP in another Database.sp_helptext does not give me drop statements.

    Originally posted by Enigma
    Ummm......

    Are you looking for this

    Code:
    sp_helptext
    Prints the text of a rule, a default, or an unencrypted stored procedure, user-defined function, trigger, or view.
    Look up sp_helptext in the Holy Book (SQL server Books online)

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    You may

    [sniped]
    Code:
    create procedure sp_helptext_modified
    @objname nvarchar(776)
    ,@columnname sysname = NULL
    as
    
    set nocount on
    
    declare @dbname sysname
    ,@BlankSpaceAdded   int
    ,@BasePos       int
    ,@CurrentPos    int
    ,@TextLength    int
    ,@LineId        int
    ,@AddOnLen      int
    ,@LFCR          int --lengths of line feed carriage return
    ,@DefinedLength int
    
    /* NOTE: Length of @SyscomText is 4000 to replace the length of
    ** text column in syscomments.
    ** lengths on @Line, #CommentText Text column and
    ** value for @DefinedLength are all 255. These need to all have
    ** the same values. 255 was selected in order for the max length
    ** display using down level clients
    */
    ,@SyscomText	nvarchar(4000)
    ,@Line          nvarchar(255)
    
    Select @DefinedLength = 255
    SELECT @BlankSpaceAdded = 0 /*Keeps track of blank spaces at end of lines. Note Len function ignores
                                 trailing blank spaces*/
    CREATE TABLE #CommentText
    (LineId	int
     ,Text  nvarchar(255) collate database_default)
    
    /*
    **  Make sure the @objname is local to the current database.
    */
    select @dbname = parsename(@objname,3)
    
    if @dbname is not null and @dbname <> db_name()
            begin
                    raiserror(15250,-1,-1)
                    return (1)
            end
    
    /*
    **  See if @objname exists.
    */
    if (object_id(@objname) is null)
            begin
    		select @dbname = db_name()
    		raiserror(15009,-1,-1,@objname,@dbname)
                    return (1)
            end
    
    -- If second parameter was given.
    if ( @columnname is not null)
        begin
            -- Check if it is a table
            if (select count(*) from sysobjects where id = object_id(@objname) and xtype in ('S ','U ','TF'))=0
                begin
                    raiserror(15218,-1,-1,@objname)
                    return(1)
                end
            -- check if it is a correct column name
            if ((select 'count'=count(*) from syscolumns where name = @columnname and id = object_id(@objname) and number = 0) =0)
                begin
                    raiserror(15645,-1,-1,@columnname)
                    return(1)
                end
        if ((select iscomputed from syscolumns where name = @columnname and id = object_id(@objname) and number = 0) = 0)
    		begin
    			raiserror(15646,-1,-1,@columnname)
    			return(1)
    		end
    
            DECLARE ms_crs_syscom  CURSOR LOCAL
            FOR SELECT text FROM syscomments WHERE id = object_id(@objname) and encrypted = 0 and number =
                            (select colid from syscolumns where name = @columnname and id = object_id(@objname) and number = 0)
                            order by number,colid
            FOR READ ONLY
    
        end
    else
        begin
            /*
            **  Find out how many lines of text are coming back,
            **  and return if there are none.
            */
            if (select count(*) from syscomments c, sysobjects o where o.xtype not in ('S', 'U')
                and o.id = c.id and o.id = object_id(@objname)) = 0
                    begin
                            raiserror(15197,-1,-1,@objname)
                            return (1)
                    end
    
            if (select count(*) from syscomments where id = object_id(@objname)
                and encrypted = 0) = 0
                    begin
                            raiserror(15471,-1,-1)
                            return (0)
                    end
    
            DECLARE ms_crs_syscom  CURSOR LOCAL
            FOR SELECT text FROM syscomments WHERE id = OBJECT_ID(@objname) and encrypted = 0
                    ORDER BY number, colid
            FOR READ ONLY
        end
    
    /*
    **  Else get the text.
    */
    /* Added By Enigma */
    select 'if exists (select name from sysobjects where name= ' + '''' + @objname + '''' + ') '
    select 'drop procedure ' + @objname
    /* Added By Enigma  ends*/
    
    SELECT @LFCR = 2
    SELECT @LineId = 1
    
    
    OPEN ms_crs_syscom
    
    FETCH NEXT FROM ms_crs_syscom into @SyscomText
    
    WHILE @@fetch_status >= 0
    BEGIN
    
        SELECT  @BasePos    = 1
        SELECT  @CurrentPos = 1
        SELECT  @TextLength = LEN(@SyscomText)
    
        WHILE @CurrentPos  != 0
        BEGIN
            --Looking for end of line followed by carriage return
            SELECT @CurrentPos =   CHARINDEX(char(13)+char(10), @SyscomText, @BasePos)
    
            --If carriage return found
            IF @CurrentPos != 0
            BEGIN
                /*If new value for @Lines length will be > then the
                **set length then insert current contents of @line
                **and proceed.
                */
                While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength
                BEGIN
                    SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded)
                    INSERT #CommentText VALUES
                    ( @LineId,
                      isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
                    SELECT @Line = NULL, @LineId = @LineId + 1,
                           @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
                END
                SELECT @Line    = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')
                SELECT @BasePos = @CurrentPos+2
                INSERT #CommentText VALUES( @LineId, @Line )
                SELECT @LineId = @LineId + 1
                SELECT @Line = NULL
            END
            ELSE
            --else carriage return not found
            BEGIN
                IF @BasePos <= @TextLength
                BEGIN
                    /*If new value for @Lines length will be > then the
                    **defined length
                    */
                    While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength
                    BEGIN
                        SELECT @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0) + @BlankSpaceAdded)
                        INSERT #CommentText VALUES
                        ( @LineId,
                          isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
                        SELECT @Line = NULL, @LineId = @LineId + 1,
                            @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
                    END
                    SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')
                    if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength+1 ) > 0
                    BEGIN
                        SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1
                    END
                END
            END
        END
    
    	FETCH NEXT FROM ms_crs_syscom into @SyscomText
    END
    
    IF @Line is NOT NULL
        INSERT #CommentText VALUES( @LineId, @Line )
    
    select Text from #CommentText order by LineId
    
    CLOSE  ms_crs_syscom
    DEALLOCATE 	ms_crs_syscom
    
    DROP TABLE 	#CommentText
    
    return (0) -- sp_helptext
    [/sniped]
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Or, you could just right-click on the object in the object-browser pane (left-hand pane) and click on "Script Object to New Window As..." and then click the sub-menu item "Create".

    You can do this with all types of objects in QA.

    Regards,

    hmscott

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •