CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
atricapilla
atricapilla

Reputation: 2640

SQL Server: how to use the same procedure in different instances?

I have a stored procedure in a test instance of a sql server (named sql2008test\sql2008_test) and it is located in one database. I would like to use this procedure also in production instance (named sql2008prod\sql2008_prod). Should I copy this procedure into this prod instance or can I modify my procedure somehow to query data from prod instance? The procedure is shown below.

CREATE PROCEDURE dbo.PROC_getDbInfo
AS
    SET NOCOUNT ON

    TRUNCATE TABLE dbo.dbinfo
    EXECUTE sp_msforeachdb 'insert into dbo.dbinfo 
            select  ''?'' as name,
                    type_desc, 
                    physical_name, 
                    state_desc, 
                    size * 1.0/128 as size_in_mb, 
                    max_size, 
                    growth * 1.0/128 as growth_in_mb, 
                    is_percent_growth,
                    is_read_only    
    from [?].sys.database_files'


    SELECT  @@SERVERNAME as instance_name,
        f.name,
        d.create_date,
        d.compatibility_level,
        d.collation_name,
        d.user_access_desc,
        d.state_desc,
        d.recovery_model_desc,
        d.page_verify_option_desc,
        d.log_reuse_wait_desc,
        f.type_desc, 
        f.physical_name, 
        f.state_desc, 
        f.size_in_mb, 
        f.max_size, 
        f.growth_in_mb, 
        f.is_percent_growth,
        f.is_read_only  
    FROM dbo.dbinfo AS f INNER JOIN
            sys.databases AS d
                ON f.name = d.name
    ORDER BY f.name
GO

Upvotes: 0

Views: 245

Answers (3)

Kashif
Kashif

Reputation: 14440

Copy your procedure into Production db.

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147324

Run the creation script on your production instance (of course, going through any release processes you have). You could set up linked servers, if you can access your production server from your test instance, but I wouldn't head down that route without a good reason.

BTW, you still have the extra GO statements in that script meaning the sproc will ONLY be created with SET NOCOUNT ON in and won't do anything.

Upvotes: 1

MahmutHAKTAN
MahmutHAKTAN

Reputation: 669

Use Server Objects>Linked Servers so you can select between different instances.

Upvotes: 0

Related Questions