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

Solution 1:

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.

Solution 2:

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@TablenameVARCHAR(100) ='tblPolicy'DECLARE@sqlVARCHAR(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. ;)