How to List Invalid objects(SP,Functions,View, Triggers) in SQL Server 2000?
Hi, I want to List all Invalid objects like Stored Procedures, Functions, Views and Triggers in SQL Server 2000 Enterprise Edition. I have written the below query to find the list of invalid objects in SQL Server 2000, I’m facing issue in storing stored procedure definition into variable nvarchar(4000) data type. nvarchar(4000) is the maximum Unicode length (since Unicode stores each character on 2 bytes). MAX function is not supported in SQL Server 2000. declare @sp_name varchar(255) declare @sp_def nvarchar(4000) declare @errorCode numeric CREATE TABLE #badViews ( name VARCHAR(255) ) DECLARE tbl_cursor CURSOR FORWARD_ONLY READ_ONLY FOR select distinct so.name, substring(sc.text,1,4000) from syscomments sc INNER JOIN sysobjects so ON
so.id where so.xtype='P' OPEN tbl_cursor FETCH NEXT FROM tbl_cursor INTO @sp_name,@sp_def; WHILE @@FETCH_STATUS = 0 BEGIN set @sp_def = REPLACE(@sp_def,CONVERT(VARCHAR(8000),N'CREATE PROCEDURE'),CONVERT(VARCHAR(8000),N'ALTER PROCEDURE')) EXEC @sp_def set @errorCode = @@error IF @errorCode <> 0 BEGIN insert into #badViews (name) values (@sp_name) END FETCH NEXT FROM tbl_cursor INTO @sp_name END CLOSE tbl_cursor; DEALLOCATE tbl_cursor; select * from #badViews drop table #badViews Text column from syscomments table has limitations while retrieving text column vales. Any other way to check the list invalid objects in SQL Server 2000. Kindly help. Thanks, Ramesh Kumar G.
I think that you have several problems here. Firstly, an error is likely to stop execution of the batch so the routine, if everything else worked, would only find the first invalid object Secondly, It looks to me as if you've taken a routine to check views to make sure that they still work (contain no invalid objects or renamed/deleted columns), and have adapted it to check procedures. the problem here is that stored procedures have parameters, and you have no way of knowing whether the error is caused by there being no expected parameter, a bad default parameter or invalid objects. The third problem is that SysComments in SQL Server 2000 are, I seem to remember, stored in chunks of 2000 characters and which have to be concatenated. Why do this when SQL Prompt already does it for you? [SQL Prompt 6: Finding invalid objects] See also a solution here, which will put you on the right track for a home-spun version but won't work for SQL Server 2000