SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Invalid Object In SQL Server 2008 database


Invalid Object In SQL Server 2008 database

Author
Message
nalinabey
nalinabey
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 185
Hi,

I am trying to find out all invalid objects in SQL Server databases.Could anyone of you give me a script if you have?Or let me know A way to find these?
Jason Selburg
Jason Selburg
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3673 Visits: 4110
Define "Invalid"

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
nalinabey
nalinabey
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 185
Hi Jason,

First of all I thank you very much and appreciate your response on this.Actually I am posting a add first time in here.Basically what I want to know is to find out status of SP's/Views/functions etc. I think in oracle we can query dba_objects and it's status column give you whether the object is valid or invalid.
Thats what I want to look at in SQL server as well. So I am looking at similar thing.

Thanks..
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28277 Visits: 39955
form some article i wrote: this is just one way to do it, there are other techniques as well, ig you search SSC, there was another recent thread on the same issue.

Objects like Views,Functions and procedures can become invalid if you drop or alter something those objects depend on. So for example, if i drop or rename a table, or even remove a column that was referenced previously by a view or procedure, those dependant objects become invalid.

If you ever get to play with other DBMS systems, you might know that Oracle keeps track of whether something is invalid or not automatically; you can look at one of the system views to check them:

select object_name from user_objects where status = 'INVALID';

The question is, how do you do the equivilent in SQL Server? The answer is there is no built-in way to check whether views, functions or procedures are valid or not. The status of whether an object is valid or not is not stored anywhere, so you have to discover it on demand.

For views, you can run the procedure sp_refreshview [viewname]; if it can be recompiled, you are all set, else it raises an error. similarly, to check a procedure or function, you have to ALTER the object and see if it fails or not. So you have to test each object in a TRY-CATCH, and see if it fails.

The script below does exactly that...checks each object, and tracks the failures. Hope this helps someone in the future.




SET NOCOUNT ON

DECLARE @BadObjects TABLE (ALLINVALIDOBJECTS NVARCHAR(4000))
DECLARE @objname NVARCHAR(4000),
@cmd NVARCHAR(MAX)
--#################################################################################################
--Views
--#################################################################################################
DECLARE acursor CURSOR FOR
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(v.name)
FROM sys.views v
INNER JOIN sys.schemas s ON v.schema_id = s.schema_id

OPEN acursor
FETCH NEXT FROM acursor INTO @objname

WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRANSACTION
BEGIN TRY
EXEC sp_refreshview @objname
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT @BadObjects VALUES (@objname)
END CATCH

FETCH NEXT FROM acursor INTO @objname
END

CLOSE acursor
DEALLOCATE acursor
--#################################################################################################
--Procs
--#################################################################################################
DECLARE c1 CURSOR FOR
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(obs.name) ,
mods.definition
FROM sys.objects obs
INNER JOIN sys.sql_modules mods ON obs.OBJECT_ID = mods.OBJECT_ID
INNER JOIN sys.schemas s ON obs.schema_id = s.schema_id
WHERE obs.is_ms_shipped = 0
AND obs.type_desc IN('SQL_STORED_PROCEDURE')
OPEN c1
FETCH NEXT FROM c1 INTO @objname,@cmd
WHILE @@fetch_status <> -1
BEGIN
BEGIN TRANSACTION
BEGIN TRY
SET @cmd = REPLACE(@cmd,CONVERT(VARCHAR(MAX),N'CREATE PROCEDURE'),CONVERT(VARCHAR(MAX),N'ALTER PROCEDURE'))
PRINT @cmd
EXEC (@cmd)
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT @BadObjects VALUES (@objname)
END CATCH

FETCH NEXT FROM c1 INTO @objname,@cmd
END --WHILE
CLOSE c1
DEALLOCATE c1
--#################################################################################################
--Functions
--#################################################################################################
DECLARE c1 CURSOR FOR
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(obs.name) ,
mods.definition
FROM sys.objects obs
INNER JOIN sys.sql_modules mods ON obs.OBJECT_ID = mods.OBJECT_ID
INNER JOIN sys.schemas s ON obs.schema_id = s.schema_id
WHERE obs.is_ms_shipped = 0
AND obs.type_desc IN('AGGREGATE_FUNCTION','SQL_INLINE_TABLE_VALUED_FUNCTION','SQL_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION')
OPEN c1
FETCH NEXT FROM c1 INTO @objname,@cmd
WHILE @@fetch_status <> -1
BEGIN
BEGIN TRANSACTION
BEGIN TRY
SET @cmd = REPLACE(@cmd,CONVERT(VARCHAR(MAX),N'CREATE FUNCTION'),CONVERT(VARCHAR(MAX),N'ALTER FUNCTION'))
PRINT @cmd
EXEC (@cmd)
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT @BadObjects VALUES (@objname)
END CATCH

FETCH NEXT FROM c1 INTO @objname,@cmd
END --WHILE
CLOSE c1
DEALLOCATE c1

SELECT * FROM @BadObjects






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!

nalinabey
nalinabey
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 185
Thanks... Lowell.it worked for me.. Smile
ausarb
ausarb
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 8
Thanks for the post! I realize this post is pretty old now, but in case it helps the next person through....


  • The view refresh fails for schema bound views. Add WHERE OBJECTPROPERTY(v.object_id, 'IsSchemaBound') = 0 to the cursor that finds views

  • If you make this into an SP yourself, you'll need to add commit transactions in the try blocks otherwise and either exclude this SP from being checked or update the find/replace part to only do it on the first CREATE PROCEDURE/FUNCTION otherwise it will change the innards of the SP and it won't work the 2nd time around.





szmynczyk
szmynczyk
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 4
Hi Lowell,

i wrote very similliar procedure as yours in my work to check procedures. My procedure throws RAISERROR when alter can't be done. It helps when it's missing column in table used in procedure which you are checking, but if in database NOT EXISTS table used in checked procedure, alter is working without error (i checked that ALTER works properly but execution of procedure throws error: 'Invalid object name table_name'. Anybody knows how to check procedure in this case?
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28277 Visits: 39955
that's a "feature" of procedures, with deferred name resolution.
there's no way to turn it off, unfortunately, as it would be very valuable to be able to do that in some cases.

this thread on stack exchange has a lot of ideas
http://stackoverflow.com/questions/4315861/why-does-microsoft-sql-server-check-columns-but-not-tables-in-stored-procs/4315884#4315884 and work arounds, like setting WITH SCHEMABINDING on the procs, but depending on your environment and whether you are "alloweD" to change the procs in that way.

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!

szmynczyk
szmynczyk
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 4
OK, i solved my problem:
USE YOUR_DB
GO
SELECT
OBJECT_NAME(referencing_id) AS CHECKED_PROCEDURE,
referenced_entity_name AS DEPENDENT_TABLE
FROM
sys.sql_expression_dependencies as ed
WHERE
is_ambiguous = 0
AND OBJECT_ID(referenced_entity_name) IS NULL AND referenced_id IS NULL
AND OBJECT_NAME(referencing_id) IN
(SELECT OBJECT_NAME(sm.object_id) AS object_name
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE o.type_desc LIKE '%PROCEDURE%')
ORDER BY
OBJECT_NAME(referencing_id), referenced_entity_name;



I added a condition to be sure that only properly tables are shown in DEPENDENT_TABLE (without it i get unknown results in this collumn) but it is properly only form my database. I hope my solution will be helpful
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search