SQL 存储过程 - 从多个数据库执行 [英] SQL Stored Procedure(s) - Execution From Multiple Databases

问题描述

我的公司使用来自许多客户的数据，而忽略了记录我们数据库的表和字段所代表的内容.为了帮助解决这个问题，我编写了一些似乎只适用于它们所在的数据库的存储过程.我想在服务器上有一个存储过程实例，可以在其所有数据库上使用，但不知道如何实现.流程如下:

My company works with data from a number of customers and has neglected to document what the tables and fields of our databases represent. To help resolve this, I wrote some stored procedures that only seem to work for the DB they live on. I would like to have one instance of the stored procedures on the server that can be used on all its databases but can't figure out how to accomplish that. Here are the procedures:

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

过程 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

过程 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

过程 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

谢谢

推荐答案

系统存储过程可以为所欲为.

A system stored procedure can do what you want.

通常，存储过程会针对编译它的数据库执行.(如您所见.)如果过程名称以sp_"开头，在master db中并用sys.sp_MS_MarkSystemObject标记，则可以这样调用:

Normally, a stored procedure executes against the database it was compiled in. (As you have noticed.) If the procedure name starts with "sp_", is in the master db and marked with sys.sp_MS_MarkSystemObject, then it can be invoked like this:

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 中，那就没问题了.

This is all fine if you can accept putting your stored procedures into master.

这篇关于SQL 存储过程 - 从多个数据库执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！