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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy