0

I would like to create a build script that can run through and recreate all views (and stored procedures too, but one step at a time).

I am trying to create a baseline of all of our user databases (around 50 of them) for our developers to run on their local machines, basically recreate all of the databases from the ground up. I have gotten past all the preliminary steps of recreating all dbs, schemas, roles, tables, indexes, constraints,...yada yada. I have relied heavily on the sp_foreachdb function and it works like a charm. Now I am stuck on stored scripts, like views and procs mostly due to the length of some of the scripts.

Reason I need to recreate the entire database setup is because people are dumb and left cross database joins and dependencies in legacy products for us. (yay!) So how can we expect our developers to pull the database down to their local machines for development and testing if first they need stuff from 3-4 other databases. And it doesn't end there, those databases depend on other things to exist. Ugh, circular dependencies.

If I only had one database I would hook up my handy dandy RedGate tool SQLCompare and pull the stuff into a script that they could run. Problem is the number of user databases we have and the desire to automate it.

If anyone can offer some advice that could help me recreate ALL script objects on ALL Databases, I would gladly accept it. The thought of manually scripting out a single database's scripts 50+ times is daunting to say the least. What am I not thinking of?

Thanks Friends!

Laura Pilon
  • 1
  • 1
  • 3

3 Answers3

2

Use SSMS... this functionality is already built in via the Generate Scripts... feature:

Similar to the instructions found here:

  1. Connect to the server hosting your database in question
  2. Expand Databases Folder
  3. Right-Click the Database -> Tasks -> Generate Scripts... enter image description here
  4. Cleck Next on the Introduction Page
  5. Choose Select specific database objects
  6. Check the box next to Views (or expand and choose only the views you care about)
  7. Click the Advanced button
  8. Adjust the settings to your liking (generally the defaults will work just fine)
  9. Click Ok
  10. (Optional) Choose the Save to new query window radio button enter image description here
  11. Click Next
  12. Click Next
  13. Click Finish to close the wizard
John Eisbrener
  • 9,427
  • 6
  • 29
  • 63
  • This suggestion would absolutely work if I only had to do 1 database (or a few), but since I have to do this for all of the databases, unfortunately that does not solve my problem. @PeterVandivier I love the idea if digging into the dba tools from Ms LaMaire. I will try that next. Thank you both for your suggestions. – Laura Pilon Dec 04 '19 at 16:19
1

Among other things, have a look at the object_definition() function and the sys.objects catalog view. See them in action here.

But yea... you should probably just use your SQLCompare tool, most RedGate tooling is the gold standard for the functionality if you've got access to it.

Peter Vandivier
  • 4,626
  • 1
  • 23
  • 45
  • Thank you so much for your suggestion. This was the first thing I tried, unfortunately we have many stored procedures (and I wouldn't put the views past them either) that exceed the nvarchar(max) that comes back from the Object_definition function. Most scripts this would work for, but there is always that jerk that writes 1500 line procs. – Laura Pilon Dec 04 '19 at 16:10
  • @Laura - IIRC, bypassing the character buffer limit is a separate issue. I definitely remember working through it though (both for this solution and the dbatools issue on the other post). From 30 sec of googling though, see - powershell, ssms hth :) – Peter Vandivier Dec 04 '19 at 19:34
1

SQL Server Database project (DACPAC) has all this functionality build in. How to: Create a New Database Project
It is similar to RedGate, but I would say it deals better with cross database references by using project references and project variables.

Piotr Palka
  • 1,581
  • 9
  • 15