Detect Stored Procedure that refer to tables that do not exist

It is possible to construct a query that will list all stored procedures
that refer to tables that no longer exist in the database?
Thanks,
ChrisHi
Execute each SP with "SET NOEXEC ON" set.
From BOL
"The execution of statements in SQL Server consists of two phases:
compilation and execution. This setting is useful for having SQL Server
validate the syntax and object names in Transact-SQL code when executing. It
is also useful for debugging statements that would usually be part of a
larger batch of statements"
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"C-W" <nomailplease@.microsoft.nospam> wrote in message
news:eFzSEi3lFHA.3900@.TK2MSFTNGP09.phx.gbl...
> It is possible to construct a query that will list all stored procedures
> that refer to tables that no longer exist in the database?
>
> Thanks,
> Chris
>|||Mike Epprecht (SQL MVP) wrote:
> Hi
> Execute each SP with "SET NOEXEC ON" set.
> From BOL
> "The execution of statements in SQL Server consists of two phases:
> compilation and execution. This setting is useful for having SQL
> Server validate the syntax and object names in Transact-SQL code when
> executing. It is also useful for debugging statements that would
> usually be part of a larger batch of statements"
> Regards
Mike,
NOEXEC doesn't seem to work for me. For some reason, it doesn't detect
the drop of the table in my example. However, when I tried using SET
FMTONLY ON, it worked and does not depend on any branching in the
procedure itself that would prevent access to the underlying missing
table.
create table abc123 (co1l int)
go
drop proc abc123test
go
create proc abc123test @.b bit
as
begin
if @.b = 0
Select co1l from abc123
else
Select id from sysobjects
end
go
exec abc123test 1
exec abc123test 0
drop table abc123
go
set fmtonly on
exec abc123test 1
exec abc123test 0
set fmtonly off
drop proc abc123test
go
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks,
Is it possible to perform the checking at the time the stored procedure is
created. The problem I have got is that it would take a long time to test a
thousand+ number of stored procedures using exec, supplying different
parameters to each one etc. So I thought it would be easier to generate a
script that attempts to drop and then recreate the stored procedures and
stop when it fails because the table does not exist.
So is it possible to perform the check when I actually do the CREATE PROC...
create proc abc123test @.b bit
as
Select co1l from abc123
go
Thanks,
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eG73N$3lFHA.2852@.TK2MSFTNGP15.phx.gbl...
> NOEXEC doesn't seem to work for me. For some reason, it doesn't detect the
> drop of the table in my example. However, when I tried using SET FMTONLY
> ON, it worked and does not depend on any branching in the procedure itself
> that would prevent access to the underlying missing table.
> create table abc123 (co1l int)
> go
> drop proc abc123test
> go
> create proc abc123test @.b bit
> as
> begin
> if @.b = 0
> Select co1l from abc123
> else
> Select id from sysobjects
> end
> go
> exec abc123test 1
> exec abc123test 0
> drop table abc123
> go
> set fmtonly on
> exec abc123test 1
> exec abc123test 0
> set fmtonly off
> drop proc abc123test
> go
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||C-W wrote:
> Thanks,
> Is it possible to perform the checking at the time the stored
> procedure is created. The problem I have got is that it would take a
> long time to test a thousand+ number of stored procedures using exec,
> supplying different parameters to each one etc. So I thought it
> would be easier to generate a script that attempts to drop and then
> recreate the stored procedures and stop when it fails because the
> table does not exist.
> So is it possible to perform the check when I actually do the CREATE
> PROC...
> create proc abc123test @.b bit
> as
> Select co1l from abc123
> go
>
> Thanks,
> Chris
>
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:eG73N$3lFHA.2852@.TK2MSFTNGP15.phx.gbl...
No. You can't do that at creation time. You could probably write
something pretty quickly to iterate through the syscolumns table for
each procedure and generate a dummy set of parameters. There is also
likely sample code out there to do the same.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks David
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23MGh30DmFHA.1372@.TK2MSFTNGP10.phx.gbl...
> No. You can't do that at creation time. You could probably write something
> pretty quickly to iterate through the syscolumns table for each procedure
> and generate a dummy set of parameters. There is also likely sample code
> out there to do the same.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||C-W wrote:
> Thanks David
> Chris
>
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:%23MGh30DmFHA.1372@.TK2MSFTNGP10.phx.gbl...
Ok. I whipped something together that you can use directly in a T-SQL
script. The script is mildly tested, so additional testing is required
at your end before using on a production system. The script creates the
necessary dynamic SQL to execute each procedure with NULL parameters and
then executes using SET FMTONLY ON and stored the results of the
execution in the temp table.
Good luck.
-- Stores the objects, execution SQL, and result
Create Table #ExecProcs (
ID INT IDENTITY NOT NULL,
Owner nvarchar(128) NOT NULL,
SPName nvarchar(128) NOT NULL,
ParameterCount INT NOT NULL,
ExecResult INT NULL,
ExecSQL nvarchar(2000) NOT NULL,
PRIMARY KEY (ID))
go
-- Get a list of procedures and create the execution SQL
Insert into #ExecProcs (
Owner,
SPName,
ParameterCount,
ExecSQL )
Select
USER_NAME(o.uid) as "Owner",
o.name as "SPName",
COUNT(p.name) as "ParameterCount",
'SET FMTONLY ON;' +
'EXEC [' +
USER_NAME(o.uid) + '].[' + o.name + '] ' +
CASE SIGN(COUNT(p.name))
WHEN 0
THEN ''
WHEN 1
THEN
REPLICATE('NULL,', COUNT(p.name) - 1) +
REPLICATE('NULL', COUNT(p.name) - (COUNT(p.name) - 1))
END + ';'
from
dbo.sysobjects o
left outer join
dbo.syscolumns p
on
o.id = p.id
where
o.type = 'P'
Group By
o.uid,
o.id,
o.name
Order By
o.uid,
o.id,
o.name
go
-- check the results
Select * from #ExecProcs
go
-- script to execute each procedure and store result in temp table
Declare @.Result INT
Declare @.SQL nvarchar(2000)
Declare @.ID INT
Declare @.Loop BIT
Set @.Loop = 1
While (@.Loop = 1)
Begin
SET @.ID = NULL
Select TOP 1
@.ID = ID,
@.SQL = ExecSQL
From
#ExecProcs
Where
ExecResult IS NULL
If @.ID IS NULL
SET @.Loop = 0
Else Begin
EXEC (@.SQL)
Update
#ExecProcs
Set
ExecResult = @.@.ERROR
Where
ID = @.ID
End
End
go
-- check results
Select * from #ExecProcs
go
-- drop the temp table
drop Table #ExecProcs
go
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David - this works great.
Thanks,
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:OuJKWnEmFHA.2904@.TK2MSFTNGP14.phx.gbl...
> C-W wrote:
> Ok. I whipped something together that you can use directly in a T-SQL
> script. The script is mildly tested, so additional testing is required at
> your end before using on a production system. The script creates the
> necessary dynamic SQL to execute each procedure with NULL parameters and
> then executes using SET FMTONLY ON and stored the results of the execution
> in the temp table.
> Good luck.