posts - 220, comments - 411, trackbacks - 27

My Links

Advertisement

News

Follow billgraziano on Twitter

Article Categories

Archives

Post Categories

Consulting

SQL Server

Scripting Objects in SQL Server 2005

After working with SQL Server 2005 I've discovered that I can't script out one object per file and include both the DROP and CREATE in the same file.  Which is driving me absolutely crazy!  So I wrote a little utility and thought I'd share it.  If you'd like to see this functionality included in a future service pack you can vote for this feature through MSDN Feedback Center.

This utility requires .NET 2.0 and the SQL Server 2005 client tools installed on the machine.  The application is deployed as a ClickOnce application.  It can be installed at:

http://www.sqlteam.com/publish/scriptio/ (Location Updated)

I haven't released the source code yet but will soon.  The application is still fairly limited in terms of error handling and such.  I also don't have the automatic error reporting wired up yet.  I haven't done as much work as I'd like testing the scripting of CLR objects yet.  It does do a darn good job of creating scripts though.  If you'd like to test it or have need of this functionality give it a whirl.

The ClickOnce install only works if you view the page through IE.  ClickOnce apparently doesn't support FireFox yet.  Oh well.  The application will automatically update as I roll out new releases.

If you have comments or feature requests please email me or post them in the comments here.  Let me know what you think.

Print | posted on Tuesday, November 22, 2005 2:58 PM | Filed Under [ SQL Server Stuff Software Development Utilities ]

Feedback

Gravatar

# re: Scripting Objects in SQL Server 2005

Excellent utility. Beyond me why MS would have left out such a feature that most DBAs/developers would make use of.
11/29/2005 9:28 AM | Philip
Gravatar

# Bill Graziano - Scripting Objects in SQL Server 2005

11/29/2005 12:16 PM | Professional Association for SQL
Gravatar

# re: Scripting Objects in SQL Server 2005

Thanks, nice tool.
I would also recommend to implement a command-line version of this tool.
12/13/2005 5:12 AM | Kaimar
Gravatar

# re: Scripting Objects in SQL Server 2005

Great utility. Throws exceptions when used with SQL 2000. Are you going to release the source soon?
1/3/2006 11:19 AM | Shane Cusson
Gravatar

# re: Scripting Objects in SQL Server 2005

The source code is available here: http://weblogs.sqlteam.com/billg/archive/2005/12/24/8613.aspx

It uses SMO so it will never work with SQL Server 2000. I'll see if I can't add some better error handling for that though.
1/3/2006 11:40 AM | graz
Gravatar

# re: Scripting Objects in SQL Server 2005

Thanks a ton for that utility.
Just one suggestion. Object level permission are not scripted, Can be included.
1/12/2006 5:06 AM | puranik_p
Gravatar

# re: Scripting Objects in SQL Server 2005

Anyone know if you can use the SMO objects to script out assemblies? Otherwise, great utility!
1/24/2006 9:06 AM | stark
Gravatar

# re: Scripting Objects in SQL Server 2005

Nevermind, I found it...SqlAssembly.
1/24/2006 9:09 AM | stark
Gravatar

# SQL authentication doesn't work

Encountered a problem: I need to connect to my 2005 server with SQL authentication. The "Server" panel connects fine, but when I attempt to actually generate the script, I get a runtime error that my SQL login is bad and a look at the stack trace indicates it's attempting to connect with my domain identity instead of the SQL login I provided.

I was able to work around the problem by connecting with the manager using the 'sa' account and adding my domain user as an admin. Then the script action worked.
2/16/2006 2:23 PM | Peter
Gravatar

# re: Scripting Objects in SQL Server 2005

Actually it should work fine with SQL2000, thats what SSMS uses to manage 2000...
2/28/2006 11:37 PM | Euan Garden
Gravatar

# Scriptio - Scripting Objects in SQL Server 2005

Bill Graziano has developed a tool called Scriptio which is available for downlo
3/6/2006 2:37 PM | some thoughts...
Gravatar

# re: Scripting Objects in SQL Server 2005

It's a relief to see that someone has done something about getting back this functionality.
Now, is there a way to get it so that is honors dependencies? Right now this scripts objects alphabetically, yet one of my 'A' tables (Address) is the last table to be dropped and the first to be recreated.
Help!

Colleen
3/8/2006 4:04 PM | Colleen McCloskey
Gravatar

# re: Scripting Objects in SQL Server 2005

This is a great tool!!! We too are confused as to why MSFT left the scripting tools off SS2005. I downloaded your tool and it shows that it has filtering, but its greyed out. Is this a future enhancement?
3/14/2006 1:33 PM | Jim Weber
Gravatar

# re: Scripting Objects in SQL Server 2005

Nice utility!
Could you please include the options to show StoredProcedurs, views, tables only?
3/16/2006 1:59 PM | Mokles
Gravatar

# re: Scripting Objects in SQL Server 2005

I added the following to the source code for allow you to script SQL 2005 -> SQL 2000

if (chkSQL2000.Checked)
baseOptions.TargetServerVersion = SqlServerVersion.Version80;
else
baseOptions.TargetServerVersion = SqlServerVersion.Version90;

AND

if (chkSQL2000.Checked)
dropOptions.TargetServerVersion = SqlServerVersion.Version80;
else
dropOptions.TargetServerVersion = SqlServerVersion.Version90;


It works great!!!
3/17/2006 11:24 AM | Chris Coursey
Gravatar

# re: Scripting Objects in SQL Server 2005

Would be really nice if there was an option to use the same file extensions as SQL Server 2003 (.PRC, .TAB, etc...)
3/21/2006 2:19 AM | Adrian
Gravatar

# re: Scripting Objects in SQL Server 2005

interesting post!

thanks, great work
4/5/2006 10:22 AM | cartes virtuelles
Gravatar

# re: Scripting Objects in SQL Server 2005

Thank you very much
Great utility!
4/6/2006 11:00 AM | telecharger mp3
Gravatar

# re: Scripting Objects in SQL Server 2005

Very Nice Tool
Thanks
I would also recommend to implement a command-line version of this tool
4/6/2006 11:03 AM | partitions musique
Gravatar

# re: Scripting Objects in SQL Server 2005

I really like the utility - but I really need it with the enhancement made by

3/17/2006 11:24 AM Chris Coursey

I'm not sure I have the tools necessary to make these modifications myself - and I certainly don't know HOW to make them myself .. any one?
4/11/2006 5:16 PM | KenBenson
Gravatar

# re: Scripting Objects in SQL Server 2005

Very cool. I want 1 obj per file for version ctrl, so I was using a keyboard recorder to export each tbl/sp, but it was buggy & very slow.

I suggest that you not export the date comment into the sql files so that changes can be tracked in version ctrl w/o false differences due to the data comment chagning. Greg
4/14/2006 9:26 PM | GregUzelac
Gravatar

# re: Scripting Objects in SQL Server 2005

The ability to choose "All Stored Procedures" or "All Tables", etc. would be nice.
4/20/2006 3:15 PM | Chris Marshall
Gravatar

# re: Scripting Objects in SQL Server 2005

Thanks for the utility.
Maybe I could suggest that you include Triggers on the next version.

Thanks again
5/2/2006 8:08 AM | RobDoyle
Gravatar

# re: Scripting Objects in SQL Server 2005

I agree that all procs or a way to filter out a specific type of object would be spectacular
5/10/2006 10:26 AM | Chuck Smith
Gravatar

# re: Scripting Objects in SQL Server 2005

hey Guy
it can be done .simply right click ok the Data base and select task, thn Generate script.it will guideu via some steps to selct ur object type and objects.
thanks
5/31/2006 6:36 AM | Nowzarth
Gravatar

# re: Scripting Objects in SQL Server 2005

This is a really great utility. Only thing i see which would be great to add is when scripting the DROP is to include drops for FK's
6/5/2006 2:15 PM | Marcello Cracolici
Gravatar

# re: Scripting Objects in SQL Server 2005

Just wanted to add another comment/suggestion. It would be great to script DEFAULT(s) associated with columns with table schema when scripting table. I do want to note this utility is awesome and appreciate all of the work you have put into it.
6/5/2006 3:00 PM | Marcello Cracolici
Gravatar

# Help needed- [how to popout the database cambo]

Hi All,
Can any body help me how i can use this tool. i read the article every body says it is a very good tool but i am unble to connect the database.

please help me. what to give in the server. and what to give in the database combo. when combo will populate so that i can select the required database.


please one and all thank for your help in advance.
6/8/2006 11:14 AM | Vijay Kumar Raja.Grandhi
Gravatar

# re: Scripting Objects in SQL Server 2005

What change would be required in the source code if I was to script a table with a new name, for example if I wanted to script authors to Tmp_authors

thanks
6/8/2006 11:28 AM | Mike
Gravatar

# re: Scripting Objects in SQL Server 2005

Hi there,
this script is not working for sql server 2000

is there any way we can create it for 2000

please help me.
6/8/2006 11:35 AM | Vijay Kumar Raja.Grandhi
Gravatar

# re: Scripting Objects in SQL Server 2005

Mike,

The easiest way to change the name is generate the script and the edit it.

-Bill
6/8/2006 11:42 AM | Bill Graziano
Gravatar

# re: Scripting Objects in SQL Server 2005

Vijay,

The application is only designed to work for SQL Server 2005. SQL Server 2000 has adequate object scripting built into Enterprise Manager.

To use the tool populate the name of the sever. You should then be able to select a database.

-Bill
6/8/2006 11:44 AM | Bill Graziano
Gravatar

# re: Scripting Objects in SQL Server 2005

This is no doubt a great tool. I need to get trigger's as well. However its giving me some problems has any one added trigger functionality to this?
Please let me know.
6/13/2006 2:01 PM | Taposh Dutta ROy
Gravatar

# re: Scripting Objects in SQL Server 2005

I managed to get the tool to work correctly with SQL 2000, but I needed to change the query that gathers information from the system catalog.

So two steps:
1. Determine which type of SQL Server instance you are using. In method PopulateDatabases() add this code after creating the Server object:

if (srv.Information.Version.Major == 8)
{
isSql2005 = false;
}
else
{
isSql2005 = true;
}

2. Then in method PopulateObjects() if the server is SQL 2000, create a new query, of form (this worked pretty well, but doubtless someone will come up with something that is more refined):

string sql2000 = @"select 'dbo' as [SchemaName],
name as [ObjectName],
case xtype
when 'U' then 'USER_TABLE'
when 'V' then 'VIEW'
when 'P' then 'SQL_STORED_PROCEDURE'
when 'FN' then 'SQL_SCALAR_FUNCTION'
when 'TF' then 'SQL_TABLE_VALUED_FUNCTION'
end as [TypeDescription]
from sysobjects
where xtype in ('U', /* user table */
'FN', /* function */
'TF', /* table valued function */
'P', /* stored procedure */
'V' /* view */
) and category <> 2
order by xtype, name";

Your tool is absolutely wonderful - I am really surprised that Microsoft, after building such an impressive class library for SQL Server didn't build this sort of functionality into the management tool directly. I can only assume that they ran out of time and that it will magically appear in a future release.

Meanwhile, by providing an example you have opened the door to enterprising folks to build custom scripting solutions - I would never have thought to look for such a class library. Now I know it exists and is pretty robust.

So thanks!
6/16/2006 7:45 AM | Dave
Gravatar

# re: Scripting Objects in SQL Server 2005

I have made changes in the code to script triggers, assemblies and enable the scripted object files to have extensions, like .prc for stored proc, .trg for triggers, .tab for table.
If any one needs info on it let me know.
Thanks to BIll for making this tool, I have add ons for it.
6/20/2006 6:18 PM | Taposh Dutta Roy
Gravatar

# re: Scripting Objects in SQL Server 2005

ITs good
7/14/2006 4:34 AM | sanjay
Gravatar

# re: Scripting Objects in SQL Server 2005

after installing, open the utility, select table or index, but nothing was displayed in the "generated script" .

Why?
7/18/2006 4:00 AM | YZ
Gravatar

# re: Scripting Objects in SQL Server 2005

Thanks for the utility. Another alternative is use your .net 2003 sever explorer to generate the script.
7/25/2006 6:56 AM | Anoop
Gravatar

# re: Scripting Objects in SQL Server 2005

A powerful SQL Server 2005 feature is the Generate Scripts Wizard. To run this wizard, open SQL Server Management Studio, then right-click a database and select Tasks, Generate Scripts. The wizard can script out all of the objects in the database including permissions, collation, and constraints. You can use this handy feature to save a database schema for versioning or create duplicate databases. And you can save the scripts to Query Explorer, the clipboard, or a file. . . .

7/25/2006 7:03 AM | Anoop
Gravatar

# re: Scripting Objects in SQL Server 2005

Anoop,

Unfortunately you can't script CREATE and DROP statements at the same time. That's why I wrote the script.

-Bill
7/25/2006 7:10 AM | Bill
Gravatar

# re: Scripting Objects in SQL Server 2005

I really need a command line utility, so I can have a batch file, command line, or script just kick off and generate script that will create all the tables (including all column defaults and such) for the specified database. Is there a command line version of this utility or a way to do this from a simple command line? I'm looking for something similar to BCP, only for generating schema rather than data...
8/3/2006 2:34 PM | Paul
Gravatar

# re: Scripting Objects in SQL Server 2005

Taposh Dutta Roy, this is exactly what I need. I neeed to be able to script out with different file exentions like .prc, .udf, .trg etc....

Could you send me the source code you used to do this?

Jonesboy21@gmail.com

8/3/2006 3:16 PM | Dustin Jones
Gravatar

# re: Scripting Objects in SQL Server 2005

Has anybody figured out a way to change the order of the SET and DROP commands? Currently the applicaion scripts the DROP first then the SET Options. For some unknown reason, all of the objects at our agency currently have the order of SET OPTIONS and then the DROP statement. To do file comparisons, this is the order we need. Any ideas?

Jonesboy21@gmail.com
8/7/2006 10:13 AM | Dustin Jones
Gravatar

# re: Scripting Objects in SQL Server 2005

I connected to the database server using my network admin account as well as the SA account but for some reason the tool doesn't show me about 35 stored procedures that I clearly see in SQL Management Studio using the same credentials.

I have confirmed in SQL Mgmt Studio that these are dbo objects, but for some reason they just don't show up in Scriptio.

Any ideas?
8/8/2006 12:01 PM | Russ
Gravatar

# re: Scripting Objects in SQL Server 2005

When you include object permissions in the script, why doesn't it include the CASCADE option?
8/23/2006 10:54 AM | Dustin Jones
Gravatar

# re: Scripting Objects in SQL Server 2005

COOL!
8/23/2006 11:23 AM | Mars
Gravatar

# re: Scripting Objects in SQL Server 2005

When I connected to a sql server 2005 server, I only saw stored procedures. Does this tool do all objects or only stored procs? Is there a setting that I need to change to include all objects?

8/31/2006 5:21 PM | ASha
Gravatar

# re: Scripting Objects in SQL Server 2005

Taposh Dutta Roy,
Can I get a copy of that version you have that scripts triggers? Do you think that the Scriptio version will have it any time soon?

Thanks
Rick

My email is rick@ the listed domain above.
9/13/2006 12:44 PM | Rick Toner
Gravatar

# re: Scripting Objects in SQL Server 2005

You da' man! Thank you very much for posting this utility.
9/15/2006 2:33 PM | Tom Regan
Gravatar

# re: Scripting Objects in SQL Server 2005

I installed the apps But do not see what to press due to generate Script?!
Please advise
EZ
9/21/2006 7:05 AM | EZ
Gravatar

# re: Scripting Objects in SQL Server 2005

Good work. Who know why the removed that feature....
2/8/2007 3:15 AM | Bronwen
Gravatar

# re: Scripting Objects in SQL Server 2005

Thanks you! Scriptio is getting a work out from me. :-) Would it be possible to add "Include USE Statement" to the Additional Scription Options?
2/12/2007 3:38 PM | Carmen Lipscomb
Gravatar

# re: Scripting Objects in SQL Server 2005

Hi,

You might want to note on the download page that installation only works via IE. At least, clicking on the application link in Firefox downloaded the .application file, which then failed to run.

However, the app itself looks great. Would be even nicer if there were a way to choose all views, or all stored procs... Aaargh, and yes, I'd really like to be able to choose old-style file extensions, so that I can compare my files to the ones I have already in CVS. But I see that the source code is there, so I may have a go at doing it myself.

Thanks & best regards,

--AndrewF
2/15/2007 8:33 AM | Andrew F
Gravatar

# re: Scripting Objects in SQL Server 2005

Hmmm, I can't get the tool to connect to the SQL 2005 engine here on my dev machine. Does it require some optional component, like SMO?
2/18/2007 8:37 AM | flipdoubt
Gravatar

# re: Scripting Objects in SQL Server 2005

Ah, I now see that one does need SMO. I also notice that I did not enter the server name correctly, but the whole SMO requirement is a bit invisible.

Works for me now. Thanks for the handy tool.
2/18/2007 8:45 AM | flipdoubt
Gravatar

# re: Scripting Objects in SQL Server 2005

Am I missing something or does this output binary files only...trying to use with VSS would be nice if it was just straight Ansi text
2/19/2007 9:17 AM | Keith
Gravatar

# re: Scripting Objects in SQL Server 2005

I should output just straight text files.
2/19/2007 9:35 AM | Bill
Gravatar

# re: Scripting Objects in SQL Server 2005

thanks a lot for this tool. it helped us a lot....
2/20/2007 11:12 PM | gopala krishna
Gravatar

# re: Scripting Objects in SQL Server 2005

Thanks!
2/28/2007 5:33 AM | Sinisa
Gravatar

# re: Scripting Objects in SQL Server 2005

Can you add an option not to include the scripting date - it means that every file is always different as far as any source control environment is concerned
3/1/2007 12:17 PM | Paul Hatcher
Gravatar

# re: Scripting Objects in SQL Server 2005

I may be missing something but I don't see a way to script the triggers.

Any chance of that getting added any time soon?
4/17/2007 1:59 PM | mike
Gravatar

# re: Scripting Objects in SQL Server 2005

Can you add option to script only Views or Tables or...?
Thanks! Good tool!
6/13/2007 5:31 AM | abrashka
Gravatar

# re: Scripting Objects in SQL Server 2005

Hi......


thanks a lot for this tool. it helped us a lot....

But bit annoying to select all the objects manually as we have more than 1000 sp's. Can you not provide the selection of object category check on which will check all the coresponding objects which can be done through a simple client scipt.

One moree thing is we have XML Schema colections to be scripted but seems not included in the module. Do you have any think of that in further release??

For sure your utility is aprreciable.......Cheers!!!
7/12/2007 2:22 AM | Venkat
Gravatar

# re: Scripting Objects in SQL Server 2005

Excellent job. I was nervous to run the one-click directly, but since I was backed into a corner and couldn't open the source code for some reason, I decided to take the leap. The scripts that were produced were right on. I can't believe MS dropped the ball on this one- seems like it should have been a piece of cake to keep this feature in 2005. Oh well. Shouldn't be surprised after all these years.

Thank you very much!
7/13/2007 11:28 AM | Charlie 69
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET