SQL 2005 Management Studio: Scripting objects

Question

SQL 2005 Management Studio: Scripting objects

Max-146500

SSCarpal Tunnel

Points: 4691
More actions
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

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

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

Clint SSC Veteran Points: 207 More actions · Answer 1

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

Ken Johnson-162971 Ten Centuries Points: 1296 More actions · Answer 2

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/

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 3

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