Scripting Tables and Procedures from SSMS

Question

Post reply

Scripting Tables and Procedures from SSMS

Steve Jones - SSC Editor

SSC Guru

Points: 736291
More actions
April 5, 2018 at 12:00 am

#77706

Comments posted to this topic are about the item Scripting Tables and Procedures from SSMS

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

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

squareoff Mr or Mrs. 500 Points: 587 More actions · Answer 1

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 Veteran Points: 240 More actions · Answer 2

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: 246970 More actions · Answer 3

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

DaveBoltman SSC Veteran Points: 203 More actions · Answer 4

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: 736291 More actions · Answer 5

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: 736291 More actions · Answer 6

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 More actions · Answer 7

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: 736291 More actions · Answer 8

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.