-- Stored Procedure: dbo.ValidateProceduresForDB
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ValidateProceduresForDB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ValidateProceduresForDB]
GO



CREATE PROCEDURE [dbo].[ValidateProceduresForDB] 
 @DBName varchar(128) 
AS
/*******************************************************************************************************
* admin.dbo.ValidateProceduresForDB 
* Creator:       bw
* Date:          02/02/2004
*
* Project:       
* Project Mgr:   
* Dev Contact:   
*     ID:      
*
* Description:   
* Notes:         
*
* Usage:
    exec admin.dbo.ValidateProceduresForDB [admin]
    exec admin.dbo.ValidateProceduresForDB [alerts]
  exec sp_msforeachdb 'exec admin.dbo.ValidateProceduresForDB ''?'''
  select * from admin.dbo.ValidateProceduresFailedLog
*
*
* Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*                    
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------
declare @Stmt nvarchar(4000)
 , @ObjectId int
 , @ObjectName varchar(128)
 , @er int
 , @hr int

---------------------------------------------
-- create temp tables
---------------------------------------------
create table #FmtOnlyExecutes 
 (ObjectId int primary key
 , FmtOnlyStmt nvarchar(1000)
 , ObjectName nvarchar(256))

---------------------------------------------
-- set session variables
---------------------------------------------
set nocount on

---------------------------------------------
-- body of stored procedure
---------------------------------------------
-- make sure the tracing log is in place
if object_id('admin.dbo.ValidateProceduresFailedLog','U') is null
 create table admin.dbo.ValidateProceduresFailedLog
  (DBName nvarchar(128)
  , ObjectName nvarchar(256)
  , ReturnCode int
  , ErrorCode int
  , RecCreatedDt datetime constraint dft_ValidateProceduresFailedLog__RecCreatedDt default (getdate()))

-- remove old data for this databse
delete admin.dbo.ValidateProceduresFailedLog
where DBName = @DBName

--Build a list or user procedures in the db to exec FMTONLY to make sure they have valid references
set @Stmt = 'use ' + quotename(@DBName) + 
    space(1) + 
    'select object_id(quotename(@DBName) + char(46) + quotename(ROUTINE_SCHEMA) + char(46) + quotename(ROUTINE_NAME)),
    ''use ' + quotename(@DBName) + ''' + 
    space(1) + 
    ''SET FMTONLY ON EXEC '' +
    quotename(ROUTINE_SCHEMA) +
    char(46) +
    quotename(ROUTINE_NAME) + 
    space(1) +
    replicate(''NULL,'',
              (select count(*) 
               from INFORMATION_SCHEMA.PARAMETERS p
               where p.SPECIFIC_SCHEMA = r.ROUTINE_SCHEMA 
               and p.SPECIFIC_NAME = r.ROUTINE_NAME)),
    quotename(ROUTINE_SCHEMA) +
    char(46) +
    quotename(ROUTINE_NAME)
from INFORMATION_SCHEMA.ROUTINES r
where r.ROUTINE_TYPE = ''PROCEDURE'' 
and objectproperty(object_id(quotename(ROUTINE_SCHEMA) + char(46) + 
    quotename(ROUTINE_NAME)), ''IsMSShipped'') = 0'

insert #FmtOnlyExecutes (ObjectId, FmtOnlyStmt, ObjectName)
exec sp_executesql @Stmt,N'@DBName varchar(128)',@DBName

select @Stmt
select * from #FmtOnlyExecutes

--remove trailing comma from parameter list 
UPDATE #FmtOnlyExecutes 
SET FmtOnlyStmt = case when right(FmtOnlyStmt, 1) = char(44)
                       then left(rtrim(FmtOnlyStmt) , datalength(FmtOnlyStmt)/2 - 1)
                       else rtrim(FmtOnlyStmt)
            end  

--run 'em and report problems
select @ObjectId = min(ObjectId) 
from #FmtOnlyExecutes

while @ObjectId is not null
 begin
  select @Stmt = FmtOnlyStmt
         , @ObjectName = ObjectName
  from #FmtOnlyExecutes
  where ObjectId = @ObjectId

  exec @hr = sp_executesql @Stmt
  set @er = @@error
  if @hr <> 0 or @er <> 0
   insert admin.dbo.ValidateProceduresFailedLog (DBName, ObjectName, ReturnCode, ErrorCode)    
   select @DBName, @ObjectName, @hr, @er

  select @ObjectId = min(ObjectId) 
  from #FmtOnlyExecutes
  where ObjectId > @ObjectId
 end

-- make sure format only doesn't get left on
-- shouldn't, but make sure anyway
SET FMTONLY OFF

RETURN


GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO