SQL Server Performance

Script to check procedures

Discussion in 'General Developer Questions' started by benwilson, Apr 10, 2005.

  1. benwilson New Member

    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
  2. dineshasanka Moderator

    Ben,
    Can't we change the same script so that it check the integrity of stored procedures and database obejcts?
  3. benwilson New Member

    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)

Share This Page