Import Existing Stored Procedures In SQL Server

Question

I restored my development database from production, and the stored procedures I need in my development environment doesn't exist in my production database. Is there a command Ii can use to import the developmetn stored procedures back into SQL Server. There are about 88 files, as each procedure is in a different text file.

TIA! Chris

Answer 1

Oops, you did the painful way of generating scripts. You should have created a single script for all procedures by right clicking on the database in SSMS, choosing Tasks -> Generate Scripts.

However, if you don't want to go through that process again, open up a cmd shell in the folder and remember those old batch file days:

for %f in (*.sql) do sqlcmd -i %f

This should do the trick! You could add other parameters to sqlcmd if required (i.e. login, password, server name, ...). To see a list of switches just do a sqlcmd -h.

Answer 2

Right click on the development database Hit Generate SQL Scripts and then only select stored precedures. If you need need additional filtering you can even select the stored procedures you dont want.

Then just run that query on development.

Answer 3

I don't know if there's a command line way to do it, but if you have them all in text files, it shouldn't be difficult at all to write a quick down and dirty app that just loops through all the files, and runs the create statements on your production server using whatever language you choose.

Answer 4

For SQL 2K & 2K5, you want this tool.

I asked a similar question awhile ago and got this advice from Mike L (give him votes here).