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:

Procedure 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

Thanks

  • SQL Server Query to compare 2 select statements data
  • NOT IN clause and NULL values
  • Make Spatial Search faster in Sql server
  • SQL Server 2014 Trouble with Convert Function
  • What is the difference between using a cross join and putting a comma between the two tables?
  • Create table syntax TSQL
  • 2 Solutions collect form web for “SQL Stored Procedure(s) – Execution From Multiple Databases”

    A system stored procedure can do what you want.

    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
    

    See: https://www.mssqltips.com/sqlservertip/1612/creating-your-own-sql-server-system-stored-procedures/

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

    You can use the undocumented system stored procedure sp_MSforeachdb, but be warned that it is undocumented and could go away at any time (although it’s been in SQL Server since at least 2005 and possibly earlier).

    Here’s an example of part of your first stored procedure using 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
    

    Also, be mindful of the threat of SQL injection depending on where that @Tablename value is coming from. There are probably some other caveats on why this isn’t a good idea, but I’ll stick with those for now. 😉

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.