Hi All, I have this really cool script (not sure where it came from) that goes through all the views in a database and checks to see that they still work (after changes in database structure, etc). What i am wondering is: does anyone know of anything similar that can be used to check stored procedures? I am currently developing a series of new tables and stored procedures, and as something changes in the tables, i have to go through a heap of procedures manually to make sure they will still work. Any ideas/solutions would be greatly appreciated! Thanks Ben
Ben, Can't we change the same script so that it check the integrity of stored procedures and database obejcts?
This is the script i use for the views- I got it from the previous DBA, and i dont know who wrote it or where he got it from, but thankyou to whoever is responsible out there!: DECLARE @SelectStatement nvarchar(4000) DECLARE @TEST as nvarchar (100) DECLARE SelectStatements CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT 'SET FMTONLY ON SELECT * FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW' AND OBJECTPROPERTY( OBJECT_ID ( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 OPEN SelectStatements WHILE 1 = 1 BEGIN FETCH NEXT FROM SelectStatements INTO @SelectStatement IF @@FETCH_STATUS = -1 BREAK EXEC(@SelectStatement) IF @@ERROR <> 0 BEGIN PRINT '=================================================================================================================================' PRINT '=================================================================================================================================' PRINT 'YES' PRINT '=================================================================================================================================' PRINT '=================================================================================================================================' PRINT '=================================================================================================================================' END END CLOSE SelectStatements DEALLOCATE SelectStatements I wouldnt be sure how to go about changing it to check SP's (especially in regards to the inserts, etc that they do)