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,547
  • 6
  • 31
  • 65
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
  • 5,485
  • 1
  • 25
  • 49
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,611
  • 11
  • 15