Deploying UDFs/SPs across all DBs

Question

I'm an accidental DBA with no scripting knowledge/background whatsoever. We have a bunch of different DBs (each client has their own) that have the same structure, but different data. That said, we regularly create functions that everyone needs to use. This means that I have to deploy these to each and every one of our 120+ DBs. How do I do that without 'manually' deploying it to every DB?

I have asked around about this before, but nothing was really helpful. The answers I got were 'use scripting' (not viable because I don't even know how to start), 'use RedGate's tool' (don't have the money for it and the company won't buy it), and 'don't use scalar UDFs to begin with' (completely unhelpful).

I've also been told 'just put it in master, or some other DB and call out to that'. That's a good thought, but soon our permissions/logins will change and I don't think that one DB user will be able to call out to another DB (HIPPA and all that).

Can anyone provide some guidance for a 'DBA' of my level?

Thanks

UPDATE: I have found that one can use sp_MSforeachdb for DDL Statements. Clearly, this is not best practice as sp_MSforeachdb is undocumented and not 'supported' by MS. As in, use at your own risk. It worked like a charm for me. See example 2: https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

For those that do not want to go that route and are in a similar boat than me, use Johnathan Fite's PS Script provided in the answers section. That's a handy script to have, especially if you have more than one server/instance.

Thanks everyone!

Answer 1

I have used a powershell script to do this before. I've included it here. The other answers about using proper tools is best, although I disagree with putting the objects in a common database for the problems you mentioned.

The script is powershell and uses windows authentication. It doesn't really do any error handling, but does have a bit that will test if the database is one of those that needs updating. Just feed it a list of SQL Instances, determine a unique table (or other query) to identify if it's one of your application databases and point it at a folder containing .sql files.

Some clarification on the script

$SQLInstanceList - This is the full path to a text file that contains all the SQL instances you want it to step over. One entry per line.

Server1
Server1\Instance1
Server2

$ScriptToRunDirectory - the directory that contains the sql files you want to run.

$Query - a query so that when it looks at a database it knows that it should run the queries against it. I put this step in because I assumed that not every database on every server is one of your application databases. For example, ReportServer, master, model, tempdb, other custom databases etc you would want to skip. I also assumed that there is at least one table that is common to all of your application databases. So when it steps over each database, it runs the above query, essentially checking to see if the specified table exists. If it does, it will run everything in the $ScriptToRunDirectory folder, otherwise it will skip it.

So the basic premise of the script is as follows: * For each SQL Instance that is listed, connect * For each database that is in the SQL instance: * run $Query to determine if it's the correct database type * If database is correct type then: * for each .sql file in $SQLToRunDirectory execute.

<#

    Name: Execute Script against Servers
    Author: Jonathan Fite
    Created: 5/3/2017

    Purpose: To execute sql scripts against a variety of servers.

    This script will take a list of servers and instances and then iterate
    over every database.  It has a section where you can query for whether
    a specific object exists.  If it exists, it will execute all the scripts
    in the specified directory.

    $SQLInstanceList - The full path and name of a text file containing the names of
        the instances to run against.  One entry per line, Ex: MyServerName\MyInstanceName

    $ScriptToRunDirectory - The full path to a directory containing the script file(s) that you want to use.
        It doesn't do recursion, but will pick up any .sql file in the directory and run it agains thte server.
        It does so in alphabetical order, so put numbers at the front of the scripts if execution sequence is important.

    $Query - This should be a query that will be run to determine if the rest of the scripts should be run or not.
        You should set it to return 0 if it's the wrong database and anything else if it's the right one.
        The example below determines if the current database (remember the script is stepping over every database)
        has the specified object.  Remember to specify the schema.

#>

# Modify the below items to the necessary values.
$SQLInstanceList = "C:\Temp\SQLInstance.txt"
$ScriptToRunDirectory = "C:\Temp"
$Query = "SELECT ISNULL(OBJECT_ID('HumanResources.Department'), 0)"

# You shouldn't need to modify anything below this line.
################################################################################################

# For Each
ForEach ($Instance In Get-Content $SQLInstanceList)
{

    Write-Host "Connecting to: " $Instance

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
    $SMO = new-object ('Microsoft.SqlServer.Management.Smo.Server') $Instance

    ForEach ($Database In ($SMO.Databases))
    {

        Write-Host "Database: $($Database.Name)"

        #Test to make sure that we want to run it against this database.
        $Results = ($SMO.Databases[$Database.Name].ExecuteWithResults($Query)).Tables[0].Rows[0][0]

        if($Results -ne 0)
        {

            Write-Host " Database Matched Criteria"

            ForEach ($File In (Get-ChildItem -Path $ScriptToRunDirectory -Filter "*.sql") | Sort-Object $_.name)
            {

                Write-Host "  - $($File.FullName)"

                $Script = [System.IO.File]::ReadAllText($File.FullName)
                Write-Host "  " $File.name 
                $Database.ExecuteNonQuery($Script)      

            }

        } else {

            Write-Host " Database Skipped"
        }

    }

}

Answer 2

If you prefer to use tsql and exclude system, offline, single user mode and Availability group read_only databases following script will do that. You will need to put your code in the section I marked with 'Put your code here'.

USE [master];
GO

DECLARE @DatabaseName VARCHAR(32)   
DECLARE @SQL NVARCHAR(max)


--get engine version
DECLARE @MajorVersion TINYINT
SET @MajorVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(4000)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(4000)))-1);

IF OBJECT_ID('tempdb..#databases') IS NOT NULL
BEGIN
DROP TABLE #databases;
END
CREATE TABLE #databases (dbname sysname);

--if pre 2012 meaning no AG
--grab all online databases
IF @MajorVersion < 11 -- pre-SQL2012
BEGIN

       INSERT INTO #databases
       --only choose online databases and exclude single user mode
       SELECT name FROM sys.databases 
       WHERE [STATE] = 0
       AND   [user_access]=0
       AND   [NAME] NOT IN ('master','model','msdb','tempdb') ; 
END
ELSE --post SQL2012
BEGIN
       INSERT INTO #databases
       ----only choose online databases and exclude single user mode
       SELECT name FROM sys.databases 
       WHERE [STATE] = 0
       AND   [user_access]=0
       AND   [NAME] NOT IN ('master','model','msdb','tempdb');  

--get secondary replica db list
IF OBJECT_ID('tempdb..#AGdatabases') IS NOT NULL
BEGIN
DROP TABLE #AGdatabases;
END

CREATE TABLE #AGdatabases
(dbname SYSNAME);

IF EXISTS (SELECT
AG.name AS [Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
    ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
    ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
    ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
)

BEGIN
INSERT INTO #AGdatabases
SELECT DISTINCT
dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
   ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
WHERE ISNULL(arstates.role, 3) = 2 AND ISNULL(dbcs.is_database_joined, 0) = 1
ORDER BY  dbcs.database_name;
END
--exclude secondary replica databases
DELETE FROM #databases WHERE dbname in
(SELECT dbname FROM #AGdatabases);
END


DECLARE Deploy_Code CURSOR LOCAL FOR
SELECT dbname FROM #databases;

OPEN Deploy_Code  
FETCH NEXT FROM Deploy_Code INTO @DatabaseName  
WHILE @@FETCH_STATUS = 0  
BEGIN  

       SELECT @SQL = 'USE '+ '[' + @DatabaseName + ']' +'; '+ 'Put your code here'+ '; '

       --EXEC sp_executesql @SQL
       PRINT @SQL

FETCH NEXT FROM Deploy_Code INTO @DatabaseName  
END  
CLOSE Deploy_Code  
DEALLOCATE Deploy_Code

Answer 3

You can try out this as given by user Cade Roux in an answer to the Stack Overflow question Common function / stored procedures for all databases:

Depending upon what the SP actually does, you want to create the procedure in master, name it with sp_ and mark it as a system procedure:

From Running a master db residing stored procedure in current database context by Mladen Prajdić:

USE master
go
CREATE PROC sp_TestMasterAccess
AS
SELECT    * 
FROM    information_schema.tables
GO
-- SS2k5 provides a stored procedure to mark the object as system
EXEC sys.sp_MS_marksystemobject sp_TestMasterAccess
GO
EXEC sp_TestMasterAccess

go
USE AdventureWorks
EXEC sp_TestMasterAccess

go
USE master 
DROP PROC sp_TestMasterAccess

Answer 4

When I need to touch multiple databases in my environment, which sounds a lot like yours, I use something like this to generate the necessary statements...which I then copy and paste to a new window to run:

Select '

use '+[name]+'
go

--replace this with your create function statement
Create View MyView
AS
Select * from sysobjects

go
'
From master..sysdatabases where [name] like '%test%'

Running this with text to results yields:

use Test
go

--replace this with your create function statement
Create View MyView
AS
Select * from sysobjects

go


use Test2
go

--replace this with your create function statement
Create View MyView
AS
Select * from sysobjects

go