Loading ...
Sorry, an error occurred while loading the content.
 

Generate script for all objects via code

Expand Messages
  • aksan kurdin
    Please help, how to coding in t-sql to generate sql script like menu command in enterprise manager [generate sql script...] ? thks in advance Aksan Kurdin
    Message 1 of 6 , Feb 22, 2005
      Please help, how to coding in t-sql to generate sql script like menu
      command in enterprise manager [generate sql script...] ?

      thks in advance

      Aksan Kurdin

      __________________________________________________
      Do You Yahoo!?
      Tired of spam? Yahoo! Mail has the best spam protection around
      http://mail.yahoo.com
    • aksan kurdin
      Discard my previous question, after googling i find this proc, may be usefull for someone here: if exists (select * from sysobjects where id =
      Message 2 of 6 , Feb 22, 2005
        Discard my previous question, after googling i find this proc, may be
        usefull for someone here:


        if exists (select * from sysobjects where id =
        object_id(N'[dbo].[s_ScriptObjects]') and OBJECTPROPERTY(id,
        N'IsProcedure') = 1)
        drop procedure [dbo].[s_ScriptObjects]
        GO

        Create procedure s_ScriptObjects
        @SourceDB varchar(128) ,
        @SourceObject varchar(128) ,
        @SourceUID varchar(128) ,
        @SourcePWD varchar(128) ,
        @OutFilePath varchar(128) ,
        @OutFileName varchar(128) ,
        @ObjectType varchar(50) -- PROCS, FUNCTIONS, TABLES
        as
        set nocount on
        /*
        exec s_ScriptObjects
        @SourceDB = 'DBName' ,
        @SourceObject = null ,
        @SourceUID = 'sa' ,
        @SourcePWD = 'Password' ,
        @OutFilePath = 'c:\' ,
        @OutFileName = 'myfile.sql' ,
        @ObjectType = 'TABLES'
        */

        declare @SourceSVR varchar(128) ,
        @ScriptType int ,
        @FileName varchar(128) ,
        @TmpFileName varchar(128) ,
        @buffer varchar(8000) ,
        @Collection varchar(128)

        declare @context varchar(255) ,
        @sql varchar(1000) ,
        @rc int

        select @SourceSVR = '(local)'

        select @ScriptType = 4 | 1 | 64 ,
        @FileName = @OutFilePath + @OutFileName ,
        @tmpFileName = @OutFilePath + 'ScriptTmp.txt'

        declare @objServer int ,
        @objTransfer int ,
        @strResult varchar(255) ,
        @strCommand varchar(255)

        -- get objects to script and object type
        create table #Objects (name varchar(128))

        if @SourceObject is not null
        insert #Objects (name)
        select @SourceObject

        if @ObjectType = 'TABLES'
        begin
        if @SourceObject is null
        begin
        select @sql = 'insert #Objects (name) '
        select @sql = @sql + 'select TABLE_NAME '
        select @sql = @sql + 'from ' + @SourceDB +
        '.INFORMATION_SCHEMA.TABLES '
        select @sql = @sql + 'where TABLE_TYPE = ''BASE TABLE'''
        exec (@sql)
        end
        select @Collection = 'tables'
        end
        else if @ObjectType = 'PROCS'
        begin
        if @SourceObject is null
        begin
        select @sql = 'insert #Objects (name) '
        select @sql = @sql + 'select ROUTINE_NAME '
        select @sql = @sql + 'from ' + @SourceDB +
        '.INFORMATION_SCHEMA.ROUTINES '
        select @sql = @sql + 'where ROUTINE_TYPE = ''PROCEDURE'''
        exec (@sql)
        end
        select @Collection = 'storedprocedures'
        end
        else if @ObjectType = 'FUNCTIONS'
        begin
        if @SourceObject is null
        begin
        select @sql = 'insert #Objects (name) '
        select @sql = @sql + 'select ROUTINE_NAME '
        select @sql = @sql + 'from ' + @SourceDB +
        '.INFORMATION_SCHEMA.ROUTINES '
        select @sql = @sql + 'where ROUTINE_TYPE = ''FUNCTION'''
        exec (@sql)
        end
        select @Collection = 'userdefinedfunctions'
        end
        else
        begin
        select 'invalid @ObjectType'
        return
        end

        -- create empty output file
        select @sql = 'echo. > ' + @FileName
        exec master..xp_cmdshell @sql

        -- prepare scripting object
        select @context = 'anywhere'
        exec @rc = sp_OACreate 'SQLDMO.SQLServer', @objServer OUT
        if @rc <> 0 or @@error <> 0 goto ErrorHnd

        exec @rc = sp_OAMethod @objServer , 'Connect', NULL, @SourceSVR ,
        @SourceUID , @SourcePWD
        if @rc <> 0 or @@error <> 0 goto ErrorHnd

        -- Script all the objects
        select @SourceObject = ''
        while exists (select * from #Objects where name > @SourceObject)
        begin
        select @SourceObject = min(name) from #Objects where name >
        @SourceObject
        select @sql = 'echo print ''Create = ' + @SourceObject + ''' >> ' +
        @FileName
        exec master..xp_cmdshell @sql
        Set @sql = 'databases("' + @SourceDB + '").' + @Collection + '("' +
        @SourceObject + '").script'
        exec @rc = sp_OAMethod @objServer, @sql , @buffer OUTPUT, @ScriptType
        , @tmpFileName
        select @sql = 'type ' + @tmpFileName + ' >> ' + @FileName
        exec master..xp_cmdshell @sql
        end
        -- delete tmp file
        select @sql = 'del ' + @tmpFileName
        exec master..xp_cmdshell @sql

        -- clear up dmo
        exec @rc = sp_OAMethod @objServer, 'Disconnect'
        if @rc <> 0 or @@error <> 0 goto ErrorHnd

        exec @rc = sp_OADestroy @objServer
        if @rc <> 0 or @@error <> 0 goto ErrorHnd

        -- clear up temp table
        drop table #Objects

        return
        ErrorHnd:
        select 'fail', @context

        go


        aksan kurdin

        --- aksan kurdin <aksankurdin@...> wrote:

        > Please help, how to coding in t-sql to generate sql script like menu
        > command in enterprise manager [generate sql script...] ?
        >
        > thks in advance
        >
        > Aksan Kurdin
        >
        > __________________________________________________
        > Do You Yahoo!?
        > Tired of spam? Yahoo! Mail has the best spam protection around
        > http://mail.yahoo.com
        >




        __________________________________
        Do you Yahoo!?
        Read only the mail you want - Yahoo! Mail SpamGuard.
        http://promotions.yahoo.com/new_mail
      • Thiam, Alioune
        Hi all, I have the following: Table1 tID title 1 Test 1 2 Test 2 3 Test 3 Table2 tID Date1 Date2 1 01/01/04 Null 1 Null 03/03/04 2 Null 01/02/05 3 02/02/05
        Message 3 of 6 , Feb 22, 2005
          Hi all,

          I have the following:

          Table1
          tID title
          1 Test 1
          2 Test 2
          3 Test 3

          Table2
          tID Date1 Date2
          1 01/01/04 Null
          1 Null 03/03/04
          2 Null 01/02/05
          3 02/02/05 Null
          3 Null 05/01/05

          The result I need
          tID Date1 Date2
          1 01/01/04 03/03/04
          2 Null 01/02/05
          3 02/02/05 05/01/05

          Any idea?

          Thanks,
          Al


          -----Original Message-----
          From: aksan kurdin [mailto:aksankurdin@...]
          Sent: Tuesday, February 22, 2005 5:53 AM
          To: sql_server@yahoogroups.com
          Cc: SQLQueriesNoCode@yahoogroups.com; sqlserver-indo@yahoogroups.com
          Subject: [SQLQueriesNoCode] Generate script for all objects via code



          Please help, how to coding in t-sql to generate sql script like menu
          command in enterprise manager [generate sql script...] ?

          thks in advance

          Aksan Kurdin

          __________________________________________________
          Do You Yahoo!?
          Tired of spam? Yahoo! Mail has the best spam protection around
          http://mail.yahoo.com



          Yahoo! Groups Links
        • John Warner
          You need to normalize your tables. John Warner
          Message 4 of 6 , Feb 22, 2005
            You need to normalize your tables.

            John Warner




            > -----Original Message-----
            > From: Thiam, Alioune [mailto:ait1@...]
            > Sent: Tuesday, February 22, 2005 3:18 PM
            > To: SQLQueriesNoCode@yahoogroups.com; sql_server@yahoogroups.com
            > Cc: sqlserver-indo@yahoogroups.com
            > Subject: RE: [SQLQueriesNoCode] Generate script for all
            > objects via code
            >
            >
            >
            >
            > Hi all,
            >
            > I have the following:
            >
            > Table1
            > tID title
            > 1 Test 1
            > 2 Test 2
            > 3 Test 3
            >
            > Table2
            > tID Date1 Date2
            > 1 01/01/04 Null
            > 1 Null 03/03/04
            > 2 Null 01/02/05
            > 3 02/02/05 Null
            > 3 Null 05/01/05
            >
            > The result I need
            > tID Date1 Date2
            > 1 01/01/04 03/03/04
            > 2 Null 01/02/05
            > 3 02/02/05 05/01/05
            >
            > Any idea?
            >
            > Thanks,
            > Al
            >
            >
            > -----Original Message-----
            > From: aksan kurdin [mailto:aksankurdin@...]
            > Sent: Tuesday, February 22, 2005 5:53 AM
            > To: sql_server@yahoogroups.com
            > Cc: SQLQueriesNoCode@yahoogroups.com; sqlserver-indo@yahoogroups.com
            > Subject: [SQLQueriesNoCode] Generate script for all objects via code
            >
            >
            >
            > Please help, how to coding in t-sql to generate sql script
            > like menu command in enterprise manager [generate sql script...] ?
            >
            > thks in advance
            >
            > Aksan Kurdin
            >
            > __________________________________________________
            > Do You Yahoo!?
            > Tired of spam? Yahoo! Mail has the best spam protection around
            > http://mail.yahoo.com
            >
            >
            >
            > Yahoo! Groups Links
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            > Yahoo! Groups Links
            >
            >
            >
            >
            >
            >
            >
            >
          • Damhuis Anton
            HiJoin table Table2 twice (2 times) to table1 each with a unique name. Then read the appropriate column.Untested Code: Select T1.tID , T2A.Date1 ,
            Message 5 of 6 , Feb 22, 2005
              Hi

              Join table Table2 twice (2 times) to table1 each with a unique name.
              Then read the appropriate column.

              Untested Code:
              Select T1.tID , T2A.Date1 , T2b.Date2
              from Table1 as T1
              Inner Join Table2 as T2A
              On T1.tID = T2A.tID
              Inner Join Table2 as T2B
              On T1.tID = T2B.tID

              Hope this helps

              Regards
              Anton


              -----Original Message-----
              From: Thiam, Alioune [mailto:ait1@...]
              Sent: 22 February 2005 10:18
              To: SQLQueriesNoCode@yahoogroups.com; sql_server@yahoogroups.com
              Cc: sqlserver-indo@yahoogroups.com
              Subject: RE: [SQLQueriesNoCode] Generate script for all objects via code




              Hi all,

              I have the following:

              Table1
              tID title
              1 Test 1
              2 Test 2
              3 Test 3

              Table2
              tID Date1 Date2
              1 01/01/04 Null
              1 Null 03/03/04
              2 Null 01/02/05
              3 02/02/05 Null
              3 Null 05/01/05

              The result I need
              tID Date1 Date2
              1 01/01/04 03/03/04
              2 Null 01/02/05
              3 02/02/05 05/01/05

              Any idea?

              Thanks,
              Al


              -----Original Message-----
              From: aksan kurdin [mailto:aksankurdin@...]
              Sent: Tuesday, February 22, 2005 5:53 AM
              To: sql_server@yahoogroups.com
              Cc: SQLQueriesNoCode@yahoogroups.com; sqlserver-indo@yahoogroups.com
              Subject: [SQLQueriesNoCode] Generate script for all objects via code



              Please help, how to coding in t-sql to generate sql script like menu
              command in enterprise manager [generate sql script...] ?

              thks in advance

              Aksan Kurdin

              __________________________________________________
              Do You Yahoo!?
              Tired of spam? Yahoo! Mail has the best spam protection around
              http://mail.yahoo.com



              Yahoo! Groups Links













              Yahoo! Groups Links








              Confidentiality Warning
              =======================
              The contents of this e-mail and any accompanying documentation
              are confidential and any use thereof, in what ever form, by anyone
              other than the addressee is strictly prohibited.
            • dudi sukma priyadi
              try this one, create table #table3 (tID int null, Date1 datetime null, Date2 datetime null) declare @tid varchar(20), @date1 datetime, @date2
              Message 6 of 6 , Feb 23, 2005
                try this one,
                 
                create table #table3 (tID int null,     Date1  datetime null,          Date2 datetime null)
                declare @tid varchar(20), @date1 datetime, @date2 datetime
                declare ms scroll cursor for select tid, date1, date2 from #table2
                open ms
                fetch first from ms into @tid, @date1, @date2
                while @@fetch_status = 0
                begin
                 if not exists(select tid from #table3 where tID=@tID)
                 insert into #table3 values (@tid, @date1, @date2)
                 else
                 update #table3 set date1= case when date1 is null then @date1 else date1 end, date2 = case when date2 is null then @date2 else date2 end where tid = @tid
                 fetch next from ms into @tid, @date1, @date2
                end
                close ms
                deallocate ms
                select * from #table
                 

                 
                -----Original Message-----
                From: "Damhuis Anton" <DamhuisA@...>
                To: <SQLQueriesNoCode@yahoogroups.com>
                Date: Wed, 23 Feb 2005 08:00:25 +0200
                Subject: RE: [SQLQueriesNoCode] Generate script for all objects via code


                Hi

                Join table Table2 twice (2 times) to table1 each with a unique name.
                Then read the appropriate column.

                Untested Code:
                Select T1.tID , T2A.Date1 , T2b.Date2
                  from Table1 as T1
                Inner Join Table2 as T2A
                       On T1.tID = T2A.tID
                Inner Join Table2 as T2B
                       On T1.tID = T2B.tID

                Hope this helps

                Regards
                  Anton


                -----Original Message-----
                From: Thiam, Alioune [mailto:ait1@...]
                Sent: 22 February 2005 10:18
                To: SQLQueriesNoCode@yahoogroups.com; sql_server@yahoogroups.com
                Cc: sqlserver-indo@yahoogroups.com
                Subject: RE: [SQLQueriesNoCode] Generate script for all objects via code




                Hi all,

                I have the following:

                Table1
                tID      title
                1      Test 1
                2      Test 2
                3      Test 3

                Table2
                tID      Date1            Date2
                1      01/01/04      Null
                1      Null            03/03/04
                2      Null            01/02/05
                3      02/02/05      Null
                3      Null            05/01/05

                The result I need
                tID      Date1            Date2
                1      01/01/04      03/03/04
                2      Null            01/02/05
                3      02/02/05      05/01/05

                Any idea?

                Thanks,
                Al


                -----Original Message-----
                From: aksan kurdin [mailto:aksankurdin@...]
                Sent: Tuesday, February 22, 2005 5:53 AM
                To: sql_server@yahoogroups.com
                Cc: SQLQueriesNoCode@yahoogroups.com; sqlserver-indo@yahoogroups.com
                Subject: [SQLQueriesNoCode] Generate script for all objects via code



                Please help, how to coding in t-sql to generate sql script like menu
                command in enterprise manager [generate sql script...] ?

                thks in advance

                Aksan Kurdin

                __________________________________________________
                Do You Yahoo!?
                Tired of spam?  Yahoo! Mail has the best spam protection around
                http://mail.yahoo.com



                Yahoo! Groups Links













                Yahoo! Groups Links








                Confidentiality Warning
                =======================
                The contents of this e-mail and any accompanying documentation
                are confidential and any use thereof, in what ever form, by anyone
                other than the addressee is strictly prohibited.
              Your message has been successfully submitted and would be delivered to recipients shortly.