Scripting Tables and Procedures from SSMS

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716562

  • squareoff

    Mr or Mrs. 500

    Points: 512

    Hi Steve - thank you for the post.

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

    Please advise.

    Thanks!

  • Duncan A. McRae

    SSC Enthusiast

    Points: 180

    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.

  • Phil Parkin

    SSC Guru

    Points: 243844

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • DaveBoltman

    SSC Enthusiast

    Points: 180

    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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716562

    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.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716562

    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.

  • moojjoo

    SSC Eights!

    Points: 957

    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.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716562

    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