Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1879
Members Online:
1
Guests Online:
35

Total Post History
Posts:
81448
Topics:
18714

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
mwojcik

Other

FAQ
All times are UTC [ DST ]

Google Ads

Best way to script database objects

Please wait...

Re: Best way to script database objects

Postby SQLDenis on Tue Jul 08, 2008 9:00 pm

The wizard is your best bet

right click on a db-->Tasks-->generate Scripts
select the DB, check script all objects at the bottom
next screen is some script setting (you could have also clicked finish at this point)
next is the summary, click finish
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond

Re: Best way to script database objects

Postby ca8msm on Tue Jul 08, 2008 9:48 pm

If you're after a freebie, I used to use this before getting Red Gate:

http://www.ssmstoolspack.com

I can't remember if it does a full db script but it was useful for generating inserts for existing data which was my main use for it.
User avatar
ca8msm
LTD Admin
LTD Admin
LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687
LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687LTD Silver - Rating: 687
 
Posts: 2492
Joined: Wed Oct 10, 2007 6:49 pm
Location: North East England

Re: Best way to script database objects

Postby onpnt on Wed Jul 09, 2008 1:43 pm

To be honest I wrote my own using SMO. I think that's the best tool if you have the develoipment knowledge to do it. THe time to make one is nothing and you can form it to your own needs pretty easily then. Think it took a few hours to write an app in C# to do functions like script all objects, script differences in objects and sync DB's.

If you want I can write up an example.
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: Best way to script database objects

Postby onpnt on Wed Jul 09, 2008 2:43 pm

Very crude example showing scripting a table and proc. Runs on my machine about half the time it takes to script it via SSMS
  1. using System;
  2. using System.Collections;
  3. using System.Collections.Specialized;
  4. using System.Collections.Generic;
  5. using System.Text;
  6. using System.Windows.Forms;
  7. using System.Data;
  8. using System.Data.SqlClient;
  9. using Microsoft.SqlServer.Management.Smo;
  10.  
  11. namespace object_scripter
  12. {
  13.     public partial class Form1 : Form
  14.     {
  15.         public Form1()
  16.         {
  17.             InitializeComponent();
  18.         }
  19.  
  20.         private void Form1_Load(object sender, EventArgs e)
  21.         {
  22.             richTextBox1.AppendText(scripter());
  23.         }
  24.  
  25.         public static string scripter()
  26.         {
  27.             Server serv = new Server("MyDBServer");
  28.             Database dbase = serv.Databases["MyDatabaseName"];
  29.  
  30.                 Table t = dbase.Tables["table_name", "dbo"];
  31.                 StoredProcedure sp = dbase.StoredProcedures["sp_name"];
  32.                 string script = "";
  33.  
  34.                 ScriptingOptions so = new ScriptingOptions();
  35.                 so.IncludeDatabaseContext = true;
  36.                  
  37.  
  38.                 StringCollection sc = t.Script(so);
  39.  
  40.                 foreach (string s in sc)
  41.                 {
  42.                     script += "\n\r" + s;
  43.                 }
  44.  
  45.                 sc.Clear();
  46.  
  47.                 sc = sp.Script(so);
  48.  
  49.                 foreach (string s in sc)
  50.                 {
  51.                     script += "\n\r" + s;
  52.                 }
  53.  
  54.             return script;
  55.          }
  56.     }
  57. }
  58.  
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI

Re: Best way to script database objects

Postby SQLDenis on Wed Jul 09, 2008 5:13 pm

Tedward,

You should add this to the SQL Admin wiki (work in progress!!) http://wiki.lessthandot.com/index.php/S ... dmin_Hacks
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Best way to script database objects

Postby onpnt on Wed Jul 09, 2008 6:08 pm

Glad it might help out riverguy. I think the only catch is ALTER's. From what I remember when you do an ALTER it gets hairy and you have to do a replace on a CREATE or something or another. I check that and post back. I could be thinking of something else

I'll get one up Denis :)
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: Best way to script database objects

Postby Devart on Mon Sep 17, 2018 12:45 pm

Hi,
I'd like to add one more great tool with a lot of awesome features that helps a lot while working with SSMS. I've found it quite recently and really impressed https://www.devart.com/dbforge/sql/sqlc ... nsion.html
Devart
Newbie
Newbie
 
Posts: 2
Joined: Mon Nov 26, 2012 3:14 pm
Unrated