Scripting Tables and Procedures from SSMS

  • Hi Steve - thank you for the post.

    When will the next article be coming out showing the powershell solution?

    Please advise.

    Thanks!

  • I recently went through this process for a fairly large database (~30000 scripts). I found the SSMS wizard cumbersome, as you point out, so I didn't want to repeat the experience for each class of entity. I scripted everything into a single folder, and used a series of MOVE commands at the command prompt:
      move *.table.sql \tables\
      move *.storedProcedure.sql \storedProcedures\

    etcetera. I found this faster than the recommended approach, and more amenable to scripting (if necessary).

    I am not sure I understand the recommendation to alter an entity in SSMS and then rescript all entities of the same type. Once the entities are scripted, is it not wiser to only edit the scripts? I find that allowing people to change entities in Object Explorer is a recipe for disaster, as they inevitably forget to update the script (and in large databases, rescripting everything to capture one change is costly). Do you automate your scripting to occur every night? That would make more sense from an overhead perspective, but you might lose auditability....

    I'll go back an re-read, I might have missed an important note or two.

  • I appreciate that this is not using SSMS, but getting this info out in a structured format is just a few clicks away if you create a database project in VS and then Import/Database.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Check out schemazen - a free command line tool that scripts outs a whole SQL Server database. It places different object type into different folders, and scripts the database options as well as the schemas separately.

    The only fault I can find with it is that it includes table valued functions in the \procedures folder.

    It can also script data for tables you choose, and create a database from a folder of scripts

  • Negmat 18367 - Thursday, April 5, 2018 7:43 AM

    Hi Steve - thank you for the post.

    When will the next article be coming out showing the powershell solution?

    Please advise.

    Thanks!

    Soon. Still cleaning some code.

  • Duncan A. McRae - Thursday, April 5, 2018 10:05 AM

    I recently went through this process for a fairly large database (~30000 scripts). I found the SSMS wizard cumbersome, as you point out, so I didn't want to repeat the experience for each class of entity. I scripted everything into a single folder, and used a series of MOVE commands at the command prompt:
      move *.table.sql \tables\
      move *.storedProcedure.sql \storedProcedures\

    etcetera. I found this faster than the recommended approach, and more amenable to scripting (if necessary).

    I am not sure I understand the recommendation to alter an entity in SSMS and then rescript all entities of the same type. Once the entities are scripted, is it not wiser to only edit the scripts? I find that allowing people to change entities in Object Explorer is a recipe for disaster, as they inevitably forget to update the script (and in large databases, rescripting everything to capture one change is costly). Do you automate your scripting to occur every night? That would make more sense from an overhead perspective, but you might lose auditability....

    I'll go back an re-read, I might have missed an important note or two.

    The move command is interesting. That's a good idea, though I think there are better ways if you're scripting.

    The reason I'd just redo the entire set of objects is that it's simple and easy. Could I edit every script? Sure, but then I get a history of:

    1. create table mytable( myid int)
    2. alter table mytable add mychar varchar(10)
    3. alter table mytable alter column myid int not null
    ...

    This  works for some people in a VCS, but over the years, I've preferred to see the whole create table statement, which I don't want to edit in addition to building the alter table script.

  • Steve, first I think you fat fingered 'ot' - 'to' ... That will make the files more similar ot what I see in Object Explorer

    Also, you can use a DB project in Visual Studio to easily manage this in Git, then you can always compare your repository to the DB and reverse.

  • Thanks, corrected.

    This is a basic article on how to capture things in SSMS. Plenty of people don't want VS, or can't use db projects depending on version, so this is focused on that part.

    Certainly a database project or a third party tool makes this much easier and more useful.

     

Viewing 9 posts - 1 through 9 (of 9 total)

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