Say we have a stored procedure that we want to copy across multiple databases. One way would be to create the script and execute in all the databases. But if Databases are many it can be time consuming and efforts to change Database name every time before executing the script will be more. One better way is writing a script that can create the Database object in selected Databases or all Databases at once.

This can be used in several ways based on different scenario, in this article we will create a very simple stored procedure just to demonstrate how this can be accomplished by SQL script.

Say, we have 3 Database as Company_1, Company_2; all of them have a table named CustomerInfo which contains customer details of its respective company. Schema of this table is described below:

CREATE TABLE [dbo].[CustomerInfo](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [FirstName] [varchar](50) NOT NULL,

    [LastName] [varchar](50) NOT NULL,

    [MiddleName] [varchar](20) NULL,

    [Address] [varchar](100) NULL,

    [City] [varchar](30) NULL,

    [Zip] [varchar](10) NULL,

    [Country] [varchar] (50) NOT NULL,

    [HomePhone] [varchar](15) NULL,

    [WorkPhone] [varchar](15) NULL,

    [EmailAddress] [varchar](100) NULL,

    [DateCreated] [datetime2](7) NULL default getdate(),

    [LastModified] [datetime2](7) NULL,

CONSTRAINT [PK_CutomerInfo] PRIMARY KEY CLUSTERED

(

    [ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Lets populate our table in both the databases with dummy data .

insert Company_1.dbo.CustomerInfo values

('Anita','Floater',NULL,'1385 Lane Dr.', 'Cary', '209856','United States', NULL,NULL,'anita@devtechie.com',getdate(),NULL)

,('Jane','Fisher',NULL,'5285 Kirk Dr .', 'Toronto', 'M4B 1V7','Canada', NULL,NULL,'Jane@devtechie.com',getdate(),'2002-08-01 00:00:00.000')

,('Ram','Sharma',NULL,'4927 S Meridian Ave Ste D', 'Puyallup', '98371','United States', NULL,NULL,'Ram@devtechie.com',getdate(),'2002-07-01 00:00:00.000')

,('Heather','Loft',NULL,'750 Lakeway Dr', 'California', '92625','United States', NULL,NULL,'Heather@devtechie.com',getdate(),NULL)

,('Xing','Xu',NULL,'7990 Ocean Beach Hwy.', 'Longview', '98632','United States', NULL,NULL,'Xing@devtechie.com',getdate(),'2002-09-01 00:00:00.000')

insert Company_2.dbo.CustomerInfo values

('Keith','Garza',NULL,'1385 Lane Dr.', 'Texas', '75201','United States', NULL,NULL,'Keith@devtechie.com',getdate(),'2003-09-01 00:00:00.000')

,('Lucy','Harris',NULL,'1318 Lasalle Street', 'Toronto', 'M4B 1V7','Canada', NULL,NULL,'Lucy@devtechie.com',getdate(),NULL)

,('Kathleen','Burnett',NULL,'4927 S Meridian Ave Ste D', 'Puyallup', '98371','United States', NULL,NULL,'Kathleen@devtechie.com',getdate(),'2002-03-01 00:00:00.000')

,('Kerim','Beck',NULL,'750 Lakeway Dr', 'California', '92625','United States', NULL,NULL,'Kerim@devtechie.com',getdate(),NULL)

,('Jinghao','Wu',NULL,'2681 Eagle Peak', 'Arizona', '85004','United States', NULL,NULL,'Jinghao@devtechie.com',getdate(),'2002-07-01 00:00:00.000')

--Note: Above I am using row constructor to insert the data.

Once our Database's are ready and table is ready in both Databases. We will write a very simple procedure of that will return all the customers whose details have never been modified.

--The script for the stored procedure will be below.

create procedure Cust_old_info

AS

begin

select FirstName, LastName ,Address,City,Zip

from dbo.CustomerInfo where LastModified is null

END

Now we will see how we can use loop to create the procedure in different database

DECLARE @sql nvarchar(4000);

DECLARE @FullSQL nvarchar(4000);

DECLARE @dbName varchar(100);

declare @ii as int

set @ii=1

declare @db as varchar(50)

declare @t as table (DBname varchar(50), i int identity(1,1))

insert @t

select distinct name from sys.databases

where

name in('company_1','company_2')

--or name like 'comapany%'

--or the condition that suits your requirements

order by name

while (@ii<=(select MAX(i) from @t))

begin

        set @dbName=(select DBName from @t where i=@ii)

                SET @sql = 'create procedure Cust_old_info

                AS

                begin

                 SET NOCOUNT ON;

                    select FirstName , LastName ,Address,City,Zip

                    from dbo.CustomerInfo where LastModified is null

                END';

        SET @FullSQL = 'USE ' + @dbName + '; EXEC sp_executesql N''' + @sql + '''';

        EXEC (@FullSQL)

        set @ii=@ii+1;

END