How to Get the Scripts for SQL Server Objects

  • Oscar D. Garcia

    SSC Eights!

    Points: 864

    Comments posted to this topic are about the item How to Get the Scripts for SQL Server Objects

    https://ozkary.com

  • Mohit K. Gupta

    SSChampion

    Points: 12130

    Thanks for article, I had found something similar at CodePlex, Link[/url].

    Only thing about CodePlex version was I couldn't get it to script out only the objects I wanted. So using DMO can give us that flexibility. Thanks.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • Martin Selway-434765

    SSC Veteran

    Points: 274

    Why is the script called with cscript rather than wscript?

    From Technet:

    [font="Courier New"] Using the command-based script host (CScript.exe)

    Cscript.exe is a command-line version of the Windows Script Host that provides command-line options for setting script properties.

    With Cscript.exe, you can run scripts by typing the name of a script file at the command prompt. Like Microsoft Internet Explorer, Windows Script Host serves as a controller of Windows Script compliant scripting engines, but Windows Script Host has very low memory requirements. Windows Script Host is ideal for both interactive and non-interactive scripting needs, such as logon scripting and administrative scripting.

    Windows Script Host supports scripts written in VBScript or JScript. When you start a script from your desktop or from the command prompt, the script host reads and passes the specified script file contents to the registered script engine. The script engine uses file extensions (that is, .vbs for VBScript and .js for JScript) to identify the script. As a result, you do not need to know the exact programmatic identifier (that is, the ProgID) of the script engine. The script host maintains a mapping of script extensions to programmatic identifiers, and uses the Windows XP association model to start the appropriate engine for a given script. For more information about Windows Script Host, see Windows Script Host For more information about CScript.exe, see To run scripts using the command-line-based script host (cscript.exe) [/font]

  • Aaron C. Sentell

    Ten Centuries

    Points: 1187

    To be fair, I have not read your article, but I did notice that it uses DMO which is being replaced by SMO. We have found that although DMO does a fair job in SQL 2005, it does not script out everything as expected in SQL 2005 (e.g., jobs, varchar(max), etc.).

    Aaron

  • Aaron C. Sentell

    Ten Centuries

    Points: 1187

    To be fair, I have not read your article, but I did notice that it uses DMO which is being replaced by SMO. We have found that although DMO does a fair job in SQL 2005, it does not script out everything as expected in SQL 2005 (e.g., jobs, varchar(max), etc.).

    Aaron

  • brettveenstra

    SSC Veteran

    Points: 232

    For scripting all your SQL objects to a Visual Studio Database project, you should do a Google search for "DbFriend".

  • brettveenstra

    SSC Veteran

    Points: 232

    For scripting all your SQL objects to a Visual Studio Database project, you should do a Google search for "DbFriend".

  • brettveenstra

    SSC Veteran

    Points: 232

    For scripting all your SQL objects to a Visual Studio Database project, you should do a Google search for "DbFriend".

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    why use this instead of SSMS to script everything out?

  • brettveenstra

    SSC Veteran

    Points: 232

    I use DbFriend for:

    - clean organization (I can put my files anywhere on disk, not just where SMSS says).

    - clean, repeatable scripts (SMSS script options are many and if I forget to check/uncheck an option, all my scripting time is wasted)

    - comparing against Production (again, clean scripting makes this a reliable DIFF)

    - DATABASE FIND & REPLACE

    - working in my environment of choice (VS 2008)

    - encourages me to THINK about my SQL assets as compiled code, that I have to RUN against a SQL box first, then exercise using SMSS (and with dual monitors, this is a very fluid workflow)

  • Oscar D. Garcia

    SSC Eights!

    Points: 864

    Hi,

    The idea behind this script is to use it for automation purposes. There are different ways in which it can be achieved. This is just one method.

    thanks for the feedback

    https://ozkary.com

  • roy-772084

    Newbie

    Points: 9

    Would it be possible to include sample output for this? I'm particularly interested to see what you get for stored procedures. Is it possible to recover the SQL that was used to create the procedures if you have admin rights to the db?

  • mmdmurphy

    Grasshopper

    Points: 21

    At the risk of sounding lazy, (it's more that I am neither a DBA nor a vb coder - I just do some becuase of my job) would it be possible to get a complete code? I am sort of at a loss as to how to combine this all into one "final" program...

  • ppcx

    Ten Centuries

    Points: 1299

    Will this script out system objects? In MSSQL 2000 I could "Create Object To New Window As Create" even on system objects (in the Master database). I can't figure out how to do that in MSSQL 2005. I need to see how some system views and functions work.

  • ppcx

    Ten Centuries

    Points: 1299

    Well, I finally found out how to Script the system objects.

    http://weblogs.sqlteam.com/mladenp/archive/2007/03/12/60132.aspx

    Shut down your database, go to the directory with the database files, copy mssqlsystemresource.mdf and mssqlsystemresource.ldf to new names, like mssqlsystemresource1.mdf and mssqlsystemresource1.ldf. Start up the database and attach your new database. Then you can Script the system stuff.

Viewing 15 posts - 1 through 15 (of 25 total)

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