跨所有数据库部署 UDF/SP

您可以按照用户Cade Roux在对 Stack Overflow 问题Common function / stored procedures for all databases的回答中进行尝试：

根据 SP 实际执行的操作，您希望在 master 中创建过程，将其命名sp_并标记为系统过程：

来自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

如果您更喜欢使用 tsql 并排除系统、离线、单用户模式和可用性组 read_only 数据库，请执行以下脚本。您需要将您的代码放在我标有“将您的代码放在这里”的部分。

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

我以前使用过 powershell 脚本来执行此操作。我把它包括在这里。关于使用适当工具的其他答案是最好的，尽管我不同意将对象放在公共数据库中以解决您提到的问题。

该脚本是 powershell 并使用 windows 身份验证。它实际上并没有做任何错误处理，但确实有一点可以测试数据库是否是需要更新的数据库之一。只需向它提供一个 SQL 实例列表，确定一个唯一表（或其他查询）以确定它是否是您的应用程序数据库之一，并将其​​指向包含 .sql 文件的文件夹。

关于脚本的一些说明

$SQLInstanceList - 这是一个文本文件的完整路径，该文件包含您希望它跳过的所有 SQL 实例。每行一个条目。

Server1
Server1\Instance1
Server2

$ScriptToRunDirectory - 包含要运行的 sql 文件的目录。

$Query - 一个查询，这样当它查看数据库时，它知道应该针对它运行查询。我加入这一步是因为我假设并非每台服务器上的每个数据库都是您的应用程序数据库之一。例如，您希望跳过的 ReportServer、master、model、tempdb、其他自定义数据库等。我还假设至少有一个表对所有应用程序数据库都是通用的。因此，当它遍历每个数据库时，它会运行上述查询，实质上是检查指定的表是否存在。如果是，它将运行 $ScriptToRunDirectory 文件夹中的所有内容，否则将跳过它。

所以脚本的基本前提如下： * 对于列出的每个 SQL 实例，连接 * 对于 SQL 实例中的每个数据库： * 运行 $Query 以确定它是否是正确的数据库类型 * 如果数据库是正确的类型然后： * 对 $SQLToRunDirectory 中的每个 .sql 文件执行。

<#

    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"
        }

    }

}

当我需要在我的环境中接触多个数据库时，这听起来很像你的，我使用类似这样的东西来生成必要的语句......然后我将其复制并粘贴到一个新窗口以运行：

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%'

使用文本运行此结果会产生：

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