invalid objects

Question

invalid objects

pshaship

SSCertifiable

Points: 6125
More actions
April 22, 2010 at 5:15 pm

#139879

Hi,
In Oracle, we will find the invalid objects using the below query.
select object_name from user_objects where status = 'INVALID';
What is the equivalent to this in SQL Server 2005?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

What do you consider to be an invalid object?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lowell SSC Guru Points: 323518 More actions · Answer 2

I had looked into this same issue back in september;

see this thread here:

http://www.sqlservercentral.com/Forums/Topic785213-146-1.aspx

at the time, i was having some developers run scripts that dropped stuff, and then they found out later their scripts dropped /renamed tables or columns that broke views they did not upgrade;

sp_refreshview view tries to recompile a view, and you need to try -catch the results to track whether it was successful. there is no built in dmv or anything to make it easy.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

SELECT * FROM sys.all_objects ]

- will return information for all these database objects .. AGGREGATE_FUNCTION

CHECK_CONSTRAINT

DEFAULT_CONSTRAINT

FOREIGN_KEY_CONSTRAINT

PRIMARY_KEY_CONSTRAINT

SQL_STORED_PROCEDURE

CLR_STORED_PROCEDURE

SQL_SCALAR_FUNCTION

CLR_SCALAR_FUNCTION

CLR_TABLE_VALUED_FUNCTION

RULE

REPLICATION_FILTER_PROCEDURE

SYSTEM_TABLE

SYNONYM

SERVICE_QUEUE

CLR_TRIGGER

SQL_TRIGGER

SQL_INLINE_TABLE_VALUED_FUNCTION

SQL_TABLE_VALUED_FUNCTION

USER_TABLE

UNIQUE_CONSTRAINT

VIEW

EXTENDED_STORED_PROCEDURE

INTERNAL_TABLE

--------------

but as far as I know and as Lowell has stated it will not identify invalid views, table , etc. but it does show what does exist. Not much help but maybe with Lowell's reference, you can meld the two together and come up with something ....

There are many additional System Views (Transact-SQL) > Catalog Views (Transact-SQL) which will return information concerning specific items for example: sys.certificates

Search Technet for additional infomation

http://technet.microsoft.com/en-us/default.aspx

using: System Views (Transact-SQL) > Catalog Views (Transact-SQL)

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

pshaship SSCertifiable Points: 6125 More actions · Answer 4

how to compile stored procedure in sql server?

in oracle, I use @package_name..

thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

That really should be a new discussion thread.

You create a stored procedure by "create procedure"

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lowell SSC Guru Points: 323518 More actions · Answer 6

procs and functions are going to be the same logic; cursor with a try - catch, but instead a dynamic SQL;

get the text for the proc/function from sys.sql_modules, do a REPLACE to make it ALTER FUNCTION instead of CREATE FUNCTION, then EXEC(@definition) in the try-catch to see if it compiles.

i recently posed a cursor you could modify in this post:

http://www.sqlservercentral.com/Forums/Topic907448-146-1.aspx#bm907658

you'd just have to add the try-catch and the saving of invalid items; the rest, where the cursor tries to recompile the function, is all there:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!