1

i'm trying to find a way to validate all sql server stored procedures. I have tried using sp_recompile and sp_refreshsqlmodule but it doesn't work.

For example, this stored procedure compiles without problem, although table doesn't exists

ALTER PROCEDURE sp_Foo
WITH RECOMPILE
AS
BEGIN
    SELECT Foo, Bar
    FROM Table_That_Dont_Exist
END

When i try to execute it throws an error.

Msg 208, Level 16, State 1, Procedure sp_Foo, Line 5
Invalid object name 'Table_That_Dont_Exist'.

Is there a way to check if a stored procedure is valid or can be executed without problem?

Thanks in advance

NioZero
  • 87
  • 2
  • 11
  • It would seem inherently difficult to validate something unless you attempt to access it; ala by executing the stored procedure. Why not execute to determine if it is in fact valid or not...? – Aaron McIver Aug 21 '15 at 19:21
  • 1
    That is because sql server has a feature known as deferred name resolution. It means that a procedure can compile even though it references objects that don't yet exist. It isn't until it needs to generate an execution plan that it would be a problem. https://technet.microsoft.com/en-US/library/ms190686.aspx – Sean Lange Aug 21 '15 at 19:21
  • My main problem is that i have a big database with a lot of stored procedures. A lot of them are invalid by a lot of reasons (rename columns, deleted tables, etc). I was trying to find a way to verify all database and check all procedures. @AaronMcIver, i tried that, but there are stored procedures that receive parameters. – NioZero Aug 21 '15 at 19:23
  • Did you see this? http://stackoverflow.com/a/13761629/175063 – Leptonator Aug 21 '15 at 19:34
  • @Leptonator SET FMTONLY or newer [sp_describe_first_result_set](https://msdn.microsoft.com/en-us/library/ff878602.aspx) has a lot of limitations, for example it won't work if stored procedure uses temp table. – Lukasz Szozda Aug 21 '15 at 19:38
  • @NioZero How many stored procedures/functions/views do you need validated? – UnhandledExcepSean Aug 21 '15 at 21:27
  • @NioZero - are you limited to a solution in SQL? – Forty3 Aug 21 '15 at 21:33
  • Technically, in SQL Server, it is *not* possible to completely validate stored procedures, because of things like deferred name resolution, dynamic SQL, dynamic name resolution, deferred and dynamic *object* resolution, temporary object creation and use, and procedural dependence on both parameter sets and data content. And by this I mean that it is *truly* impossible (see "Halting Problem") with *any* tools/methods ever. The best you can do is to execute it and see. The next best that you can do is to use one of the methods (`FMTONLY`, etc.) that will give you a partial answer. – RBarryYoung Aug 21 '15 at 21:44
  • @NioZero - If you have access to a Visual Studio environment, the answer provided here [Using TSqlParser](http://stackoverflow.com/a/25778275/3485669) can be used as a foundation to check the SPs for validity - by looping through them, extracting the tablenames in use, and then verifying that the tables actually exist. It would require some work on your part (the sample seems to only work with SELECT statements) but it would be a start. – Forty3 Aug 21 '15 at 23:23
  • @Leptonator, that's only solves part of the problem.. – NioZero Aug 25 '15 at 17:49
  • @Ghost, the quantity doesn't matter, i was only trying to find an automatic method – NioZero Aug 25 '15 at 17:49
  • @Forty3, that's the ideal case... In other RDBMS i always try to use only-SQL... And using Visual Studio is an option, but not always available, some project are in private server that you can only access through Management Studio, but then again, that's only solves part of the problem. – NioZero Aug 25 '15 at 17:50
  • @RBarryYoung now with the answers I can conclude that there is no single solution to solve the whole problem but many solutions that solve only part of the problem. – NioZero Aug 25 '15 at 17:50
  • Thanks everyone with the answers... – NioZero Aug 25 '15 at 17:50

3 Answers3

1

You could script out all off your stored procedures using the Tasks -> Generate Scripts feature. Save off all your scripts into a single text file, and then create a parser to programatically parse the text and determine the objects to check. This will be VERY difficult to get even close to 100% correct. I was looking into actually running the functions and procedures, but the problem is that if the values passed in cause blocks of code to be skipped, then it doesn't validate them.

This can validate all your views though

SELECT
    'SELECT TOP 1 * FROM ' + obj.name + ' GO;' [SQL]
FROM sys.objects obj
WHERE obj.[TYPE] IN ('V')

It's a start.

UnhandledExcepSean
  • 12,504
  • 2
  • 35
  • 51
1

This is a decent starting point to find procedures, views etc that have missing object references.

select *
from sys.sql_expression_dependencies
where referenced_id is null
    AND referenced_server_name is null --excludes link server missing items
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • 1
    This was not intended as a complete solution. Notice I said missing object references. As you mentioned in your comments there is no single way to find all these issues. – Sean Lange Aug 25 '15 at 18:08
0

Here is what I use to check all my Procedures, Views and Functions:

declare @dbname nvarchar(50) = '<your database name here>.'

declare @t1 table(dbname nvarchar(50), [type] nvarchar(2), name nvarchar(50), script nvarchar(max))
declare @t2 table(dbname nvarchar(50), [type] nvarchar(2), name nvarchar(50), error nvarchar(max))

declare @s1 nvarchar(max), @s2 nvarchar(max), @name nvarchar(50), @type nvarchar(2), @script nvarchar(max)

set @s1 =
'
select ''@dbname'', o.[type], o.name
, (case when o.[type] in (''V'',''TF'') then ''select top 1 * from '' else ''exec '' end) + ''[@dbname].[dbo].['' + o.name + '']''
+ (case when o.[type] = ''TF'' then ''('' else '''' end) +
isnull(replace(replace(replace((select ''NULL'' s from @dbname.INFORMATION_SCHEMA.PARAMETERS 
where specific_name = o.name and parameter_mode = ''in'' for xml path ('''')), ''</s><s>'', '',''), ''<s>'', '' ''), ''</s>'', ''''), '''') 
+ (case when o.[type] = ''TF'' then '')'' else '''' end) script
from @dbname.sys.objects o
where o.[type] in (''P'',''V'',''TF'',''FN'') 
and ascii(left(o.name, 1)) > 64 
and left(o.name, 3) <> ''dt_''
'

set @s2 = replace(@s1,'@dbname', @dbname)
insert @t1 exec (@s2)

declare c cursor for select dbname, [type], name, script from @t1
open c

fetch next from c into @dbname, @type, @name, @script

while @@fetch_status = 0   
begin
    begin try
        exec(@script)
    end try
    begin catch
        insert @t2 select @dbname, @type, @name, error_message()
    end catch

    fetch next from c into @dbname, @type, @name, @script
end

close c
deallocate c

select * from @t2
order by dbname, [type], name
Stagger Lee
  • 1
  • 2