Post reply

Command to generate scripts in sql server 2005

  • September 25, 2008 at 3:01 am

    #124811

    Hi All,

    I want to know if there is any command to generate scripts in sql server 2005.

    I have used the Tasks -> Generate Scripts Feature in Sql Server Management Studio.

    But want to do the same from command or sp.

    Please reply with the suggestions to achieve the same.

    Thanks,

    Amit Khanna

  • September 25, 2008 at 3:22 am

    #876841

    try scriptio from the MS SQL team, installable from

    http://www.sqlteam.com/publish/scriptio

    and the .NET source-code is also available.

    The underlying code library is SMO so you can cobble together code to do what you want easily enough.

    Previous era with scripting may be helpful if you're more at home with this

    http://www.microsoft.com/technet/scriptcenter/hubs/sqlserver.mspx

    HTH

    Dick

  • September 25, 2008 at 3:34 am

    #876845

    Can u please send me the link where the Source Code is available.

    I had alread tried it but unable to find source code.

    Thanks,

    Amit Khanna

  • September 25, 2008 at 3:52 am

    #876855

    SCRIPTIO was initially written by Bill Graziano in MS SQLTeam but now lives at

    http://www.codeplex.com/scriptio

    so you can get latest, code, docs etc from there

    Dick

  • September 25, 2008 at 3:54 am

    #876858

    Hi All,

    I have tried this below sp. But it shows the Script of Length 256 only. Can anyone please

    tell how to increase the Length.

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_genscript]')

    and OBJECTPROPERTY(id, N'IsProcedure') = 1)

    drop procedure [dbo].[proc_genscript]

    GO

    CREATE PROCEDURE proc_genscript

    @ServerName varchar(30),

    @DBName varchar(30),

    @ObjectName varchar(50),

    @ObjectType varchar(10),

    @TableName varchar(50),

    @ScriptFile varchar(255)

    AS

    DECLARE @CmdStr varchar(8000)

    DECLARE @object int

    DECLARE @hr int

    SET NOCOUNT ON

    SET @CmdStr = 'Connect('+@ServerName+')'

    EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT

    --Comment out for standard login

    EXEC @hr = sp_OASetProperty @object, 'LoginSecure', TRUE

    /* Uncomment for Standard Login

    EXEC @hr = sp_OASetProperty @object, 'Login', 'sa'

    EXEC @hr = sp_OASetProperty @object, 'password', 'sapassword'

    */

    EXEC @hr = sp_OAMethod @object,@CmdStr

    SET @CmdStr =

    CASE @ObjectType

    WHEN 'Database' THEN 'Databases("'

    WHEN 'Procedure'THEN 'Databases("' + @DBName + '").StoredProcedures("'

    WHEN 'View' THEN 'Databases("' + @DBName + '").Views("'

    WHEN 'Table'THEN 'Databases("' + @DBName + '").Tables("'

    WHEN 'Index'THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Indexes("'

    WHEN 'Trigger'THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Triggers("'

    WHEN 'Key'THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Keys("'

    WHEN 'Check'THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Checks("'

    WHEN 'Job'THEN 'Jobserver.Jobs("'

    END

    SET @CmdStr = @CmdStr + @ObjectName + '").Script(5,"' + @ScriptFile + '")'

    EXEC @hr = sp_OAMethod @object, @CmdStr

    EXEC @hr = sp_OADestroy @object

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply