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

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

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

谢谢