SQL Server Performance Forum – Threads Archive
Objects validity
Hello!<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br />In Oracle I check sometimes the validity of objects this way:<br /><br />Select object_name, object_type, owner<br />from dba_objects<br />where status=’invalid’;<br /><br />Should I do something similair in MS SQL?<br />And what is a way to do so?<br /><br />
List out objects from INFORMATION SCHEMA views by using :
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE=’Object (SP or view)’ AND(OBJECT_ID(ROUTINE_NAME), ‘IsMSShipped’) 1 _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I’m very sorry…….. I’m checking the MS Books Online and cannot understand your responce corrently<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />(<br />IsMSShipped= An object created during installation of Microsoft® SQL Serverâ„¢ 2000. <br />1 = True<br />0 = False<br /><br />IsMSShipped equal to Invalid?<br /><br />Please, explain me your responce more detailed if you can….<br />Thank you very much in advance…….<br />
In your case IsMSShipped=0 is object invalid. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Thank you very much!!!
One more way to find out is when you execute query in QA use SET SHOWPLAN_TEXT or parse the quer which will give the result. (just found out…the way) _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
ok, If I found that one of trigger is invalid and I would like to recompile it, do
I have something similair to Oracle:
Alter trigger compile?
Alter package compile body?
I found only EQModel.Compile([SQLConnection], [OLAPConnection])-Performs all object validation checks on each object in the project and returns a collection of any errors, warnings, and hints that are found.
You could use SP_RECOMPILE to compile a SP or trigger on next time they run.
(I have no exp. in Oracle) _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>