SQL Stored Procedure(s) - Execution From Multiple Databases

Question

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

`sp_` naming convention, for user defined db objects, is bad practice — Lukasz Szozda, Jan 20 '16 at 18:00
Here is an article covering why sp_ is a bad practice. http://sqlperformance.com/2012/10/t-sql-queries/sp_prefix — Sean Lange, Jan 20 '16 at 18:03
You would have to implement dynamic sql to make this a generic set of procedures so that you can define which database it will execute against. It can be done but would be a fair amount of work because in addition to actually making it function with dynamic sql it also needs to be safe from sql injection. — Sean Lange, Jan 20 '16 at 18:07
I tried to check out the link above, but it was broken. I will do some research on my own to better understand the best practices here. Ultimately, I flagged the stored procedures as system objects, and that allowed me to execute them from anywhere on the server with the desired scope. Thank you everyone for your feedback. — fireflyfiend, Jan 20 '16 at 22:19

score 3 · Accepted Answer · answered Jan 20 '16 at 19:29

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.

This worked perfectly for what I wanted it to do. The article was also very informative. Thanks for your help! — fireflyfiend, Jan 20 '16 at 22:16

score 0 · Answer 2 · answered Jan 20 '16 at 18:17

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. ;)

Thank you for the feedback, Tom. I ultimately marked my stored procedure as a system object to facilitate the desired functionality. After doing some deeper research, I don't think SQL injection will be a significant concern for my final solution. Thanks for the suggestion and words of caution. — fireflyfiend, Jan 20 '16 at 23:06

SQL Stored Procedure(s) - Execution From Multiple Databases

Procedure 1 - sp_GetTableDocumentation

Procedure 2 - sp_SetTableDocumentation

Procedure 3 - sp_SetTableDescription

Procedure 4 - sp_SetColumnDescription

2 Answers2

Linked