invalid object like view, function etc.

[Fran?ois Bourdages]

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.

[Dan Guzman]

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 #ValidateObject s
AS
SET NOCOUNT ON
--procedures and functions
SELECT
CASE r.ROUTINE_TYPE
WHEN 'PROCEDURE' THEN 'Procedure'
WHEN 'FUNCTION' THEN
CASE
WHEN
OBJECTPROPERTY( OBJECT_ID(QUOTE NAME(ROUTINE_SC HEMA) +
N'.' +
QUOTENAME(ROUTI NE_NAME)), 'IsTableFunctio n') = 1
THEN 'TableFunction'
WHEN
OBJECTPROPERTY( OBJECT_ID(QUOTE NAME(ROUTINE_SC HEMA) +
N'.' +
QUOTENAME(ROUTI NE_NAME)), 'IsScalarFuncti on') = 1
THEN 'ScalarFunction '
WHEN
OBJECTPROPERTY( OBJECT_ID(QUOTE NAME(ROUTINE_SC HEMA) +
N'.' +
QUOTENAME(ROUTI NE_NAME)), 'IsInlineFuncti on') = 1
THEN 'InlineFunction '
END
END AS ObjectType,
QUOTENAME(ROUTI NE_SCHEMA) +
N'.' +
QUOTENAME(ROUTI NE_NAME) AS ObjectName,
REPLICATE(N'NUL L,',
ISNULL((SELECT COUNT(*) AS Parameters
FROM INFORMATION_SCH EMA.PARAMETERS p
WHERE
p.IS_RESULT = 'NO' AND
p.SPECIFIC_SCHE MA = r.ROUTINE_SCHEM A AND
p.SPECIFIC_NAME = r.ROUTINE_NAME) , 0)) AS Parameters
INTO #Objects
FROM INFORMATION_SCH EMA.ROUTINES r
WHERE
ROUTINE_TYPE IN ('PROCEDURE', 'FUNCTION') AND
OBJECTPROPERTY( OBJECT_ID(QUOTE NAME(ROUTINE_SC HEMA) +
N'.' +
QUOTENAME(ROUTI NE_NAME)), 'IsMSShipped') = 0
UNION ALL
--views
SELECT
'View' AS ObjectType,
QUOTENAME(TABLE _SCHEMA) +
N'.' +
QUOTENAME(TABLE _NAME) AS ObjectName,
'' AS Parameters
FROM INFORMATION_SCH EMA.TABLES t
WHERE
TABLE_TYPE = 'VIEW' AND
OBJECTPROPERTY( OBJECT_ID(QUOTE NAME(TABLE_SCHE MA) +
N'.' +
QUOTENAME(TABLE _NAME)), 'IsMSShipped') = 0

--remove trailing comma from parameter list
UPDATE #Objects
SET Parameters = LEFT(Parameters , LEN(Parameters) - 1)
WHERE RIGHT(Parameter s, 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(ObjectNam e) +
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 InvocationScrip t
INTO #InvocationScri pts
FROM #Objects
ORDER BY ObjectName

DECLARE InvocationScrip ts CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT InvocationScrip t
FROM #InvocationScri pts
DECLARE @InvocationScri pt nvarchar(4000)
OPEN InvocationScrip ts
WHILE 1 = 1
BEGIN
FETCH NEXT FROM InvocationScrip ts INTO @InvocationScri pt
IF @@FETCH_STATUS = -1 BREAK
SET @InvocationScri pt = 'PRINT ''' +
@InvocationScri pt +
''' SET FMTONLY ON ' + @InvocationScri pt + ' SET FMTONLY OFF'
EXEC sp_executesql @InvocationScri pt
END
CLOSE InvocationScrip ts
DEALLOCATE InvocationScrip ts

DROP TABLE #Objects, #InvocationScri pts
GO
--
Hope this helps.

Dan Guzman
SQL Server MVP

"Fran?ois Bourdages" <fr************ ****@harfan.com > wrote in message
news:92******** *************** ***@posting.goo gle.com...

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.