Retrieve all stored procedures from all databases in SQL Server 2008

Question

Can anyone advise you to get a list of all stored procedures along with your schema names in the entire database? Thanks for your input!

Answer 1

CREATE TABLE #x(db SYSNAME, s SYSNAME, p SYSNAME); DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N'INSERT #x SELECT ''' + name + ''',s.name, p.name FROM ' + QUOTENAME(name) + '.sys.schemas AS s INNER JOIN ' + QUOTENAME(name) + '.sys.procedures AS p ON p.schema_id = s.schema_id; ' FROM sys.databases WHERE database_id > 4 EXEC sp_executesql @sql; SELECT db,s,p FROM #x ORDER BY db,s,p; DROP TABLE #x; 

Answer 2

For all databases, to search for user-defined stored procedures, views, functions, and table functions, the following can help (based on the Habo above):

 declare @Sprocs as Table (DBName VarChar(64), SchemaName varchar (128), StoredProcName VarChar(128), type varchar(10), type_desc varchar(128), create_date datetime, modify_date datetime, is_MS_Shipped int) insert into @Sprocs execute sp_MSforeachdb N'use ?; select ''?'', s.name as SchemaName, o.name as StoredProcName, type, type_desc, create_date, modify_date, is_MS_Shipped from sys.all_objects as o inner join sys.schemas as s on s.schema_id = o.schema_id where o.type in (''P'', ''V'', ''PC'', ''FN'', ''TF'') and is_ms_shipped = 0' select * from @Sprocs where DBName not in ('master', 'tempdb', 'model', 'msdb', 'dba') order by DBName, SchemaName, type, StoredProcName 

Add 'U' to get user-created tables as well.

Answer 3

For one database:

 select s.name + '.' + p.name as sproc_name_with_schema from sys.procedures as p inner join sys.schemas as s on s.schema_id = p.schema_id 

Do you need this for all databases? Your question is a bit unclear.

 declare @Sprocs as Table ( DBName VarChar(64), SprocName VarChar(128) ) insert into @Sprocs execute sp_MSforeachdb N'use ?; select ''?'', s.name + ''.'' + o.name from sys.all_objects as o inner join sys.schemas as s on s.schema_id = o.schema_id where o.type = ''P''' select * from @Sprocs 

Answer 4

Alternative to first answer without temporary table and inserts:

 declare @sql varchar(max) = ''; select @sql += ' ' + 'union all ' + 'select ''' + dat.name + ''' as bas, b.name as sch, a.name as pro ' + 'from ' + '[' + dat.name + '].[sys].procedures as a ' + 'inner join [' + dat.name + '].[sys].schemas as b on a.schema_id = b.schema_id' from sys.databases as dat; set @sql = RIGHT(@sql, LEN(@sql) - 11); exec(@sql); print @sql;