SQL Server doesn't expose this information. One method to identify invalid
objects is to reference them with SET FMPONLY ON:
SET FMTONLY ON
SELECT * FROM MyView
SET FMTONLY OFF
GO
SET FMTONLY ON
EXEC MyProcedure NULL
SET FMTONLY OFF
GO
However, this is not as thorough as actually exercising the objects. For
example, it won't detect invalid dynamic SQL or triggers. Below is a proc
that will generate and execute such a script for all views, functions and
procedures in the database. Note that it is still under development and
hasn't been tested thoroughly.
CREATE PROC #ValidateObjects
AS
SET NOCOUNT ON
--procedures and functions
SELECT
CASE r.ROUTINE_TYPE
WHEN 'PROCEDURE' THEN 'Procedure'
WHEN 'FUNCTION' THEN
CASE
WHEN
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)), 'IsTableFunction') = 1
THEN 'TableFunction'
WHEN
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)), 'IsScalarFunction') = 1
THEN 'ScalarFunction'
WHEN
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)), 'IsInlineFunction') = 1
THEN 'InlineFunction'
END
END AS ObjectType,
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS ObjectName,
REPLICATE(N'NULL,',
ISNULL((SELECT COUNT(*) AS Parameters
FROM INFORMATION_SCHEMA.PARAMETERS p
WHERE
p.IS_RESULT = 'NO' AND
p.SPECIFIC_SCHEMA = r.ROUTINE_SCHEMA AND
p.SPECIFIC_NAME = r.ROUTINE_NAME), 0)) AS Parameters
INTO #Objects
FROM INFORMATION_SCHEMA.ROUTINES r
WHERE
ROUTINE_TYPE IN ('PROCEDURE', 'FUNCTION') AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)), 'IsMSShipped') = 0
UNION ALL
--views
SELECT
'View' AS ObjectType,
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS ObjectName,
'' AS Parameters
FROM INFORMATION_SCHEMA.TABLES t
WHERE
TABLE_TYPE = 'VIEW' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
--remove trailing comma from parameter list
UPDATE #Objects
SET Parameters = LEFT(Parameters, LEN(Parameters) - 1)
WHERE RIGHT(Parameters, 1) = N','
--generate invocation scripts
SELECT
CASE ObjectType
WHEN 'View' THEN 'SELECT * FROM '
WHEN 'Procedure' THEN 'EXEC '
WHEN 'ScalarFunction' THEN 'SELECT '
WHEN 'InlineFunction' THEN 'SELECT * FROM '
WHEN 'TableFunction' THEN 'SELECT * FROM '
END +
RTRIM(ObjectName) +
CASE ObjectType
WHEN 'View' THEN ''
WHEN 'Procedure' THEN ' '
WHEN 'ScalarFunction' THEN '('
WHEN 'InlineFunction' THEN '('
WHEN 'TableFunction' THEN '('
END +
Parameters +
CASE ObjectType
WHEN 'View' THEN ''
WHEN 'Procedure' THEN ''
WHEN 'ScalarFunction' THEN ')'
WHEN 'InlineFunction' THEN ')'
WHEN 'TableFunction' THEN ')'
END
AS InvocationScript
INTO #InvocationScripts
FROM #Objects
ORDER BY ObjectName
DECLARE InvocationScripts CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT InvocationScript
FROM #InvocationScripts
DECLARE @InvocationScript nvarchar(4000)
OPEN InvocationScripts
WHILE 1 = 1
BEGIN
FETCH NEXT FROM InvocationScripts INTO @InvocationScript
IF @@FETCH_STATUS = -1 BREAK
SET @InvocationScript = 'PRINT ''' +
@InvocationScript +
''' SET FMTONLY ON ' + @InvocationScript + ' SET FMTONLY OFF'
EXEC sp_executesql @InvocationScript
END
CLOSE InvocationScripts
DEALLOCATE InvocationScripts
DROP TABLE #Objects, #InvocationScripts
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Fran?ois Bourdages" <fr****************@harfan.com> wrote in message
news:92**************************@posting.google.c om...
Hi is there a way to know if object (view, function, etc) are invalid
?
let say a have a table t1 (field col1, col2)
and a view v1 (field t1.col1, t1.col2)
if I drop t1.col2, the view v1 is not working anymore. I want to know
that information.
In Oracle (8.1.7), i can query the all_objects, user_object table,
where status = 'INVALID'. So i can recompile invalid objects (or
correct it).
In sql Server, the table sysobjects give me some status info, but
they are not documented enough.
Do you know if i can user one of those fields : status, userstat,
sysstat ?
Same question for function , procedure.
TKS.
