I am new to SQL, and what I needed to do was to combine 2 .mdf databases into one. I did that using SQL Server 2008 Manager - Tasks > Import/Export tables.The tables and views were copied successfully, but there are no Stored procedures in the new database. Is there any way to do that?
Launch MS SQL Server Management Studio in your system and go to the Object Explorer. Step 2. Right-click on the database from which you want to move data and then click on Tasks>>Generate Scripts… A Generate and Publish Scripts Wizard will appear on the screen, click on the Next button to proceed.
This code copies all stored procedures in the Master database to the target database, you can copy just the procedures you like by filtering the query on procedure name.
@sql is defined as nvarchar(max), @Name is the target database
DECLARE c CURSOR FOR SELECT Definition FROM [ResiDazeMaster].[sys].[procedures] p INNER JOIN [ResiDazeMaster].sys.sql_modules m ON p.object_id = m.object_id OPEN c FETCH NEXT FROM c INTO @sql WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = REPLACE(@sql,'''','''''') SET @sql = 'USE [' + @Name + ']; EXEC(''' + @sql + ''')' EXEC(@sql) FETCH NEXT FROM c INTO @sql END CLOSE c DEALLOCATE c
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With