Query to find invalid objects?

Is there a system table that holds data on invalid objects/procedures? In
Oracle, I can query dba_objects table where status='INVALID' to find all of
the invalid objects. Is there anything similar in SQL Server?
thanks!
SusanHi Susan
What do you mean by invalid objects?
If you require to know any stored procedure that is referencing another
stored procedure or table etc.. that does not exists then you may want to try
doing a textual search of the source code. There is a sysdepends table for
dependencies, but this is not guaranteed to list everything.
John
"Susan Cooper" wrote:
> Is there a system table that holds data on invalid objects/procedures? In
> Oracle, I can query dba_objects table where status='INVALID' to find all of
> the invalid objects. Is there anything similar in SQL Server?
> thanks!
> Susan|||Can you define what an "invalid" object is? This is not a term used in the SQL Server world, so it
could mean different things. The short story is "no", there is no such table...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
> Is there a system table that holds data on invalid objects/procedures? In
> Oracle, I can query dba_objects table where status='INVALID' to find all of
> the invalid objects. Is there anything similar in SQL Server?
> thanks!
> Susan|||By invalid, I mean that dependencies are broken. I was hoping that SQL
Server stored something saying a procedure will not work if an object it
depends on is dropped or changed, ect. In the Oracle world, these are called
invalid objects and you can query the dba_objects table.
Thanks for the help!
"Tibor Karaszi" wrote:
> Can you define what an "invalid" object is? This is not a term used in the SQL Server world, so it
> could mean different things. The short story is "no", there is no such table...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
> news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
> > Is there a system table that holds data on invalid objects/procedures? In
> > Oracle, I can query dba_objects table where status='INVALID' to find all of
> > the invalid objects. Is there anything similar in SQL Server?
> >
> > thanks!
> > Susan
>|||Hi
You may want to look at using SCHEMABINDING with views and functions (which
is the converse of what you wanted!), but for stored procedures it is not
available.
John
"Susan Cooper" wrote:
> By invalid, I mean that dependencies are broken. I was hoping that SQL
> Server stored something saying a procedure will not work if an object it
> depends on is dropped or changed, ect. In the Oracle world, these are called
> invalid objects and you can query the dba_objects table.
> Thanks for the help!
> "Tibor Karaszi" wrote:
> > Can you define what an "invalid" object is? This is not a term used in the SQL Server world, so it
> > could mean different things. The short story is "no", there is no such table...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
> > news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
> > > Is there a system table that holds data on invalid objects/procedures? In
> > > Oracle, I can query dba_objects table where status='INVALID' to find all of
> > > the invalid objects. Is there anything similar in SQL Server?
> > >
> > > thanks!
> > > Susan
> >
> >|||As already posted, the sp_depends and sysdependencies tables (and the 2005 catalog view counterpart)
will give you a clue. But it isn't 100 percent for reasons like dynamic SQL, deferred name
resolution etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
news:951437A6-A3F4-493D-A17C-4E753FC9E526@.microsoft.com...
> By invalid, I mean that dependencies are broken. I was hoping that SQL
> Server stored something saying a procedure will not work if an object it
> depends on is dropped or changed, ect. In the Oracle world, these are called
> invalid objects and you can query the dba_objects table.
> Thanks for the help!
> "Tibor Karaszi" wrote:
>> Can you define what an "invalid" object is? This is not a term used in the SQL Server world, so
>> it
>> could mean different things. The short story is "no", there is no such table...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
>> news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
>> > Is there a system table that holds data on invalid objects/procedures? In
>> > Oracle, I can query dba_objects table where status='INVALID' to find all of
>> > the invalid objects. Is there anything similar in SQL Server?
>> >
>> > thanks!
>> > Susan
>>