470,596 Members | 1,460 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,596 developers. It's quick & easy.

invalid object like view, function etc.

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.
Jul 20 '05 #1
1 4293
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.

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

12 posts views Thread by R | last post: by
reply views Thread by Fran?ois Bourdages | last post: by
1 post views Thread by Owen Jenkins | last post: by
2 posts views Thread by Jerry Nelson | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.