July 12, 2011 at 9:02 am
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?
July 12, 2011 at 9:04 am
Define "Invalid"
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJuly 12, 2011 at 9:18 am
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..
July 12, 2011 at 9:21 am
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
July 12, 2011 at 9:56 am
Thanks... Lowell.it worked for me.. 🙂
January 30, 2013 at 8:11 am
Thanks for the post! I realize this post is pretty old now, but in case it helps the next person through....
August 28, 2014 at 2:43 am
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?
August 28, 2014 at 5:44 am
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
August 28, 2014 at 8:06 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply