Generating scripts using T-SQL

  • When you use Enterprise Manager, "All Tasks", one of the selections is "generate script". You go in there, set some options and it generates files that generally do NOT use the .SQL extension. I remember .TAB, .TRG, etc.

    Is there anyway to invoke this process using T-SQL directly in Query Analyzer to generate these script files?

    Thanks!

  • Well assuming that you have SQL 2000 (you're in a 2005 forum, by the way), then you can generate them one at a time in Query Analyzer:

    Hit F8 to open the Object Explorer pane. Select the object that you want to script in teh Object Explorer pane, right click on it an select "Script Object".."to New Query Window".

    Also, you can get Enterprise Manager to put those scripts in a .SQL file. You just need to set the option to put them all in a single file, instead of a different file for each object.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Oops ... I am using SS2K! Sorry.

    I know how to generate the scripts using EM and QA (one at a time). I was just trying to see if there were a script I could run in QA that would use a cursor to iterate tables, Views, sp, udf, triggers and generate a script file for each.

    My limited experience is that whenever MS has an automation wizard, there is another way to do the same thing.

    Thanks for your help!

  • Tom Carnahan (1/21/2009)


    My limited experience is that whenever MS has an automation wizard, there is another way to do the same thing.

    You are right, this is generally true. And in fact you can get the listing of Views, sProcs and triggers easily enough from the syscomments table. Tables are another matter, however. The ability to script tables is NOT in SQL Server itself, but rather in the DMO libraries that SQL managment clients, like EM, use. This is true of all of the "static" objects (those without executable code), however, tables are special in that they are so complicated, that it is not easy to write your own script generator for them in SQL.

    There are some other ways to do it (using sp_OA calls, etc.), but I am not familiar with them and I am not in a position to test SQL 2000 stuff anyway.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • for views,procedures,functions (and triggers) you could use a cursor to exec sp_helptext [objectname]

    that would give you the script for those objects, assuming they were not marked WITH ENCRYPTION when they were created.

    getting the DDL for Tables are harder. I think there is a few contributions in the SCRIPTs section here at SSC to help you do it.

    I've done this for an app we use at work to compare a schema against a "published" XML for the database version. Esentially, I created the DDL for the table by looping thru all the columns in syscolumns for a given Id of a table; it gets kind of invloved but a terrific learning experience, especially when you start adding in retrieving the IDENTITY columns and starting values, indexes, default constraints and all PK/UQ and regualr constriants or indexes.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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