Post reply

SQL 2005 Management Studio: Scripting objects

  • February 13, 2006 at 9:32 am

    #167099

    Hi,

    I've been trying to script out some objects from a 2005 database in Management Studio. There’s an excellent tool called the Script Wizard but as far as I can see, which isn't that well at the moment, it doesn’t cater for scripting out the selected objects to individual files by object name, it seems that it dumps all the selected objects into 1 file.

    Does anyone have a work around or some SMO\DMO code to run from SSIS? The SMO code would be excellent as I’ve isolated some procs and UDF’s that I’d like to script out to individual files. Only thing about SMO\DMO is that I’m really not that proficient at it

    Many thanks

    Max

  • February 13, 2006 at 2:53 pm

    #620542

    Hi, Max,

    Here's some code that will give you access to many objects' source code at once, which you can then export to Excel or some other tool.

    Hope this helps,

    Clint

    select

     so.name,  -- Object name

     sc.text,  -- T-SQL used to create object

     sc.colid  -- If T-SQL used to create object > 4,000 characters,

               -- it will be stored in multiple chunks.  

               -- ColID is used to store the chunk order

    from dbo.syscomments sc

    inner join sysobjects so

        on sc.id = so.id 

     -- XType V = View

    where so.xtype = 'v'

    order by

    so.name, sc.colid

  • February 13, 2006 at 7:56 pm

    #620563

    This isn't built into SSMS. However, there is a free (0.5 relaese) utility called scriptio that you can find here:

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

  • February 14, 2006 at 2:55 am

    #620604

    Hi Clint and Ken

    Thanks for your replies. I was looking to export specific items to file, without going through the system catalogue. Clint check http://msdn2.microsoft.com/en-us/library/ms187997.aspx for sql 2000 mappings to sql 2005 objects.

    Scriptio is a good tool for scripting a small selection or the entire database and the options to include schemas and of course "one file per object" are brilliant. Scriptio does have one draw back though; if you have thousands of programmable objects (procs, udf's...) that you'd like to script out, there isn't functionality to allow you to select the object types that you want to script out.

    Once again, many thanks for your suggestions.

    Max

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

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