September 24, 2010 at 10:54 am
All,
I am having issue with trying to figure out which stored procedures are invalid, short of executing all of them. I've looked through the INFORMATION_SCHEMA.ROUTINES table, the sys.objects, sys.procedures and still cannot find the status. Is there a way in SQL Server to tell if a Stored Procedure is invalid?
Thanks,
Harry
September 24, 2010 at 11:04 am
thre is not an In-Your-Face status like Oracle and other languages have; all you can do is test all the objects and save the results in a table for the pass fail; I had asked the same thing a while back.
As a result of another thread, I built this snippet of code, which I run after an "upgrade" script is executed, to see if we broke anything.
see if this helps you out:
SET NOCOUNT ON
DECLARE @BadObjects TABLE (ALLINVALIDOBJECTS nvarchar(4000))
DECLARE @objname NVARCHAR(4000),
@cmd NVARCHAR(max)
--#################################################################################################
--Views
--#################################################################################################
DECLARE acursor CURSOR FOR
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(v.name)
FROM sys.views v
INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
OPEN acursor
FETCH NEXT FROM acursor INTO @objname
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRANSACTION
BEGIN TRY
exec sp_refreshview @objname
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT @BadObjects VALUES (@objname)
END CATCH
FETCH NEXT FROM acursor INTO @objname
END
CLOSE acursor
DEALLOCATE acursor
--#################################################################################################
--Procs
--#################################################################################################
DECLARE c1 CURSOR FOR
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(obs.name) ,
mods.definition
FROM sys.objects obs
INNER JOIN sys.sql_modules mods ON obs.object_id = mods.object_id
INNER JOIN sys.schemas s ON obs.schema_id = s.schema_id
WHERE obs.is_ms_shipped = 0
AND obs.type_desc IN('SQL_STORED_PROCEDURE')
OPEN c1
FETCH NEXT FROM c1 INTO @objname,@cmd
WHILE @@fetch_status <> -1
BEGIN
BEGIN TRANSACTION
BEGIN TRY
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'CREATE PROCEDURE'),convert(varchar(max),N'ALTER PROCEDURE'))
print @cmd
exec (@cmd)
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT @BadObjects VALUES (@objname)
END CATCH
FETCH NEXT FROM c1 into @objname,@cmd
END --WHILE
CLOSE c1
DEALLOCATE c1
--#################################################################################################
--Functions
--#################################################################################################
DECLARE c1 CURSOR FOR
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(obs.name) ,
mods.definition
FROM sys.objects obs
INNER JOIN sys.sql_modules mods ON obs.object_id = mods.object_id
INNER JOIN sys.schemas s ON obs.schema_id = s.schema_id
WHERE obs.is_ms_shipped = 0
AND obs.type_desc IN('AGGREGATE_FUNCTION','SQL_INLINE_TABLE_VALUED_FUNCTION','SQL_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION')
OPEN c1
FETCH NEXT FROM c1 INTO @objname,@cmd
WHILE @@fetch_status <> -1
BEGIN
BEGIN TRANSACTION
BEGIN TRY
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'CREATE FUNCTION'),convert(varchar(max),N'ALTER FUNCTION'))
print @cmd
exec (@cmd)
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT @BadObjects VALUES (@objname)
END CATCH
FETCH NEXT FROM c1 into @objname,@cmd
END --WHILE
CLOSE c1
DEALLOCATE c1
SELECT * FROM @BadObjects
Lowell
September 24, 2010 at 11:13 am
Thanks Lowell,
Would this script actually execute the Stored Procedures? Since its a test system with active users, I want to make sure that the SQL wont execute all of the SPs.
September 24, 2010 at 11:20 am
harry.chandra (9/24/2010)
Thanks Lowell,Would this script actually execute the Stored Procedures? Since its a test system with active users, I want to make sure that the SQL wont execute all of the SPs.
naw, the script does not execute them...if you look at the details, it's doing a ALTER PROCEDURE command with the text from sys.sql_modules, so it just tries to re-compile the procedure, not CALL the procedure.
Lowell
September 24, 2010 at 11:22 am
Perfect. I'll try it out.
Thanks,
June 6, 2012 at 1:41 pm
An alternative solution (that does not require compiling any objects) would be to write a TSQL script that identifies & loops through all user-defined stored procs, functions, and views; and in each iteration, perform the following:
1. Find any/all db object references (via the sp_depends system stored procedure).
2. Check for the existence of referenced db object(s) (via the object_id system function).
3. Print the name of the “bad” stored proc/function/view.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply