SQL 存储过程 - 从多个数据库执行

Question

我的公司与许多客户的数据打交道，但我们忽略了记录数据库中表格和字段的含义。为了解决这个问题，我编写了一些存储过程，但似乎只适用于它们所在的数据库。我希望在服务器上有一个存储过程的实例，可以用于所有数据库，但是我不知道如何实现这一点。以下是存储过程：

存储过程1 - sp_GetTableDocumentation

Create Procedure sp_GetTableDocumentation(@TableName SYSNAME)
AS

SELECT
    @TableName AS [Table Name]
    ,'' AS [Column Name]
    ,CONVERT(NVARCHAR(MAX), ISNULL(D.value, '')) AS [Description]
FROM sys.Tables AS T
OUTER APPLY (SELECT TOP 1 * FROM ::fn_listextendedproperty('Description', 'SCHEMA', 'dbo', 'TABLE', @TableName, NULL, NULL)) AS D
WHERE T.Name = @TableName

UNION ALL

SELECT
    @TableName AS [Table Name]
    ,C.Name AS [Column Name]
    ,CONVERT(NVARCHAR(MAX), ISNULL(D.value, '')) AS [Description]
FROM sys.Tables AS T
INNER JOIN sys.Columns AS C ON T.Object_id = C.Object_id
OUTER APPLY (SELECT TOP 1 * FROM ::fn_listextendedproperty('Description', 'SCHEMA', 'dbo', 'TABLE', @TableName, 'COLUMN', C.Name)) AS D
WHERE T.Name = @TableName
GO

Procedure 2 - sp_SetTableDocumentation

Create Procedure sp_SetTableDescription(
  @schemaName sysname
  , @tableName sysname
  , @description sql_variant
)
As
    If Exists (
      Select 1 
      From fn_listextendedproperty('Description','SCHEMA',@schemaName,'TABLE',@tableName,NULL,NULL)
    )
        exec sp_DropExtendedProperty 'Description','SCHEMA',@schemaName,'TABLE',@tableName 
        If (Not @description Is Null) And (Not @description = '')
        exec sp_AddExtendedProperty 'Description', @description,'SCHEMA',@schemaName,'TABLE',@tableName
GO

Procedure 3 - sp_SetTableDescription

Create Procedure sp_SetTableDescription(
  @schemaName sysname
  , @tableName sysname
  , @description sql_variant
)
As
If Exists (
    Select 1 
    From fn_listextendedproperty('Description','SCHEMA',@schemaName,'TABLE',@tableName,NULL,NULL)
)
    exec sp_DropExtendedProperty 'Description','SCHEMA',@schemaName,'TABLE',@tableName
If (Not @description Is Null) And (Not @description = '')
exec sp_AddExtendedProperty 'Description', @description,'SCHEMA',@schemaName,'TABLE',@tableName
GO

Procedure 4 - sp_SetColumnDescription

CREATE PROCEDURE sp_SetColumnDescription (
    @schemaName SYSNAME
    ,@tableName SYSNAME
    ,@columnName SYSNAME
    ,@description SQL_VARIANT
    )
AS
IF EXISTS (
        SELECT 1
        FROM fn_listextendedproperty('Description', 'SCHEMA', @schemaName, 'TABLE', @tableName, 'COLUMN', @columnName)
        )
    EXEC sp_DropExtendedProperty 'Description','SCHEMA',@schemaName,'TABLE',@tableName,'COLUMN',@columnName

IF (NOT @description IS NULL) AND (NOT @description = '')
    EXEC sp_AddExtendedProperty 'Description',@description,'SCHEMA',@schemaName,'TABLE',@tableName,'COLUMN',@columnName
GO

谢谢

- fireflyfiend

---

sp_ 命名约定对于用户定义的数据库对象来说是不好的实践。 - Lukasz Szozda

这是一篇介绍为什么 sp_ 不是良好编程实践的文章。http://sqlperformance.com/2012/10/t-sql-queries/sp_prefix - Sean Lange

您需要实现动态SQL，以使其成为一组通用的存储过程，以便您可以定义它将执行哪个数据库。虽然这是可行的，但需要相当多的工作，因为除了实际上使用动态SQL使其功能正常外，还需要防止SQL注入漏洞。 - Sean Lange

我尝试访问上面的链接，但它已经失效了。我将自己进行一些研究，以更好地理解这里的最佳实践。最终，我将存储过程标记为系统对象，这使我能够在服务器上任何地方以所需范围执行它们。谢谢大家的反馈。 - fireflyfiend

Answer 1

一个系统存储过程可以实现你想要的功能。

通常，存储过程会在它编译的数据库中执行。（正如你所注意到的。）如果该存储过程的名称以"sp_"开头，在master数据库中，并且被标记为sys.sp_MS_MarkSystemObject，则可以像这样调用它：

Exec somedb.dbo.sp_GetTableDocumentation
Exec anotherdb.dbo.sp_GetTableDocumentation

参见：https://www.mssqltips.com/sqlservertip/1612/creating-your-own-sql-server-system-stored-procedures/ 如果您可以接受将存储过程放入master中，那么这一切都很好。

- Roy Latham

---

这对我想要实现的功能非常完美。这篇文章也非常有启发性。感谢你的帮助！ - fireflyfiend

Answer 2

你可以使用未记录的系统存储过程sp_MSforeachdb，但请注意它是未记录的，随时可能消失（尽管它至少自2005年以来一直存在于SQL Server中，可能更早）。

以下是使用sp_MSforeachdb的第一个存储过程部分的示例：

DECLARE @Tablename VARCHAR(100) = 'tblPolicy'
DECLARE @sql VARCHAR(MAX) =
'USE [?]

SELECT
    T.TABLE_NAME AS [Table Name],
    '''' AS [Column Name],
    CONVERT(NVARCHAR(MAX), ISNULL(D.value, '''')) AS Description
FROM
    INFORMATION_SCHEMA.TABLES T
OUTER APPLY (SELECT TOP 1 * FROM ::fn_listextendedproperty(''Description'', ''SCHEMA'', ''dbo'', ''TABLE'', ''' + @TableName + ''', NULL, NULL)) AS D
WHERE
    TABLE_NAME = ''' + @Tablename + ''''

EXEC master.sys.sp_MSforeachdb @sql

此外，要注意根据@Tablename值的来源，SQL注入的威胁。可能还有其他一些注意事项，但我现在只说这些。;)

- Tom H

---

感谢你的反馈，Tom。为了实现所需的功能，我最终将我的存储过程标记为系统对象。经过更深入的研究，我认为SQL注入对于我的最终解决方案不会构成重大问题。感谢你的建议和警告。 - fireflyfiend