Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DBASlut
Yak Posting Veteran
71 Posts |
 Posted - 2006-03-08 : 15:31:37
|
| I want to script out all the objects of my database without using the Enterprise Manager.i can pull the procs out of syscomments, but what about the tables? |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2006-03-08 : 16:13:35
|
| How's your VB or VBS knowledge (or anything else that can talk to COM) ?Enterprise Manager uses a component called SQL-DMO to do all it's scripting tricks. You can write some code to generate DB scripts with it.There is an intro to DMO programming here http://www.sqlteam.com/item.asp?ItemID=9093 and an example in C# here http://www.sqlteam.com/item.asp?ItemID=17320The C# example is relevant as it uses the Transfer object. While you can script individual objects as shown in the intro article, if you use the transfer object to script everything it takes into account database dependencies, so everything gets built in the right order.That should get you started in the right direction.Damian"A foolish consistency is the hobgoblin of little minds." - Emerson |
 |
|
|
DBASlut
Yak Posting Veteran
71 Posts |
Posted - 2006-03-08 : 16:46:20
|
| I was hoping to be able to do this using T-SQL. For the procs I am doing:SELECT DISTINCT'GO'+ CHAR(10) +'IF EXISTS(SELECT id FROM sysobjects WHERE name ='''+object_name(id)+''' AND type =''p'')' + char(10)+' drop proc ' + object_name(id) + char(10) + 'GO' + CHAR(10) +TEXT + CHAR(10)FROM syscomments WHERE ID IN(SELECT ID FROM SYSOBJECTS WHERE TYPE ='P')This will generate the script in the output window. I then will copy to a new window and execute.I need one for actual tables.. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2006-03-08 : 17:17:13
|
| You could make it up out of SELECT * FROM INFORMATION_SCHEMA.Columns.However, you are much better off doing it my way, you'll get constraints, defaults, everything else, and you'll get them scripted in the right order so you can actually use your script to do something useful.Damian"A foolish consistency is the hobgoblin of little minds." - Emerson |
|
|
|
DBASlut
Yak Posting Veteran
71 Posts |
Posted - 2006-03-17 : 15:45:26
|
| thanks Merkin.. i like that idea..I'll try it. |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-03-20 : 05:45:39
|
| http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.htmlThis is tsql but it's easily converted to vbs.There's also a script to automatically update the data in sourcesafe as well.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Scripting out all objects..
The title was originally "Creating a 'Create Table' script from a stored procedure" - but somehow that got edited down and is now a little non-sensical!Ryan Randallwww.monsoonmalabar.comIdeas are easy. Choosing between them is the hard part.