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