Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Invalid Object In SQL Server 2008 database Expand / Collapse
Author
Message
Posted Tuesday, July 12, 2011 9:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 20, 2014 3:44 AM
Points: 14, Visits: 161
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?

Post #1140394
Posted Tuesday, July 12, 2011 9:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:01 PM
Points: 2,717, Visits: 3,856
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
Post #1140396
Posted Tuesday, July 12, 2011 9:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 20, 2014 3:44 AM
Points: 14, Visits: 161
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..
Post #1140411
Posted Tuesday, July 12, 2011 9:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 12,921, Visits: 32,287
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1140415
Posted Tuesday, July 12, 2011 9:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 20, 2014 3:44 AM
Points: 14, Visits: 161
Thanks... Lowell.it worked for me.. :)
Post #1140467
Posted Wednesday, January 30, 2013 8:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 4, 2013 9:51 PM
Points: 1, 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.




Post #1413651
Posted Thursday, August 28, 2014 2:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:51 AM
Points: 2, 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?
Post #1608191
Posted Thursday, August 28, 2014 5:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 12,921, Visits: 32,287
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1608224
Posted Thursday, August 28, 2014 8:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:51 AM
Points: 2, 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
Post #1608293
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse