Code has no parameters or variables. Just creates a Temp tbl, then grabs data from catalogs into the Temp tbl, then select from temp tbl while INSERT into table on management instance. Always works.
Howe ever when I add CREATE PROC <name> as and then EXEC <name> is does not fail but the proc does nothing as in no values are created. What am I missing about converting into a PROC that would impact its behaviour if executed just as T-SQL?
SET NOCOUNT ON;
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N'[tempdb].[dbo].[ReplStats]') ) DROP TABLE [tempdb].[dbo].[tbl]