asked
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
sc.id=
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 don't understand the problem. Is the issue that you can't see all the text from the query you're running or is the issue that you want to determine when ad hoc queries being built in your t-sql statements are violating the string limits? Or both? It's not clear to me what the issue is from your question. Why are you doing so much ad hoc and dynamic query work? That's usually a very poor way to write T-SQL code.
Hi Grint, My main goal is to retrieve List invalid objects in SQL Server 2000. I couldn't get any solution for this. If there is any other way to check then Kindly help.
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][1] 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
http://www.sqlservercentral.com/Forums/Topic1140394-1550-1.aspx [1]:
http://documentation.red-gate.com/display/SP6/Finding+invalid+objects
Hi Phil, Many Thanks for your reply. I checked REDGATE Product SQL Prompt. Will it support SQL Server 2000 Enterprise? Thanks, Ramesh Kumar G.
16 People are following this question.
