Check all SPs from a script

It's known that the SQL Server doesn't check the SPs and views when we
modify the tables and if we try to use these old SPs after we applied any
modifications like column names to the tables we'll get very serious
problems.
Is it possible to check all SPs and View from one script, like open each
one, check if it's correct (maybe try to save?) and if it's a problem then
save the name of this wrong SP to some table or just provide a list with
these names?
Dmitri.Oh, thats a good one, the only thing that come in mind would be to figure
out all Procedures (in all sysobjects) and to identify for every single
procedure the parameters in syscolumns. Then you have to create a
SQLStatement with the paramters and some dummy values within an automatic
Rollback transaction and query after execution for the @.@.error to identify
wheter it was executed valid or not.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Just D." <no@.spam.please> schrieb im Newsbeitrag
news:zwthe.10252$D91.4971@.fed1read01...
> It's known that the SQL Server doesn't check the SPs and views when we
> modify the tables and if we try to use these old SPs after we applied any
> modifications like column names to the tables we'll get very serious
> problems.
> Is it possible to check all SPs and View from one script, like open each
> one, check if it's correct (maybe try to save?) and if it's a problem then
> save the name of this wrong SP to some table or just provide a list with
> these names?
> Dmitri.
>|||Hi
That is by design, it is called Deferred Name Resolution.
You can overcome the problem in the views with using WITH SCHEMABINDING when
creating the view.
For validation, here is some code that checks if the objects referenced
exist.
--Credit to Dan Guzman, SQL Server MVP:
SELECT
N'SET FMTONLY ON EXEC ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS ProcedureName,
REPLICATE(N'NULL,',
ISNULL(
(SELECT COUNT(*) AS Parameters
FROM INFORMATION_SCHEMA.PARAMETERS p
WHERE p.SPECIFIC_SCHEMA = r.ROUTINE_SCHEMA AND
p.SPECIFIC_NAME = r.ROUTINE_NAME), 0)
) AS Parameters
INTO #FmtOnlyExecutes
FROM INFORMATION_SCHEMA.ROUTINES r
WHERE r.ROUTINE_TYPE = 'PROCEDURE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTI
NE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)), 'IsMSShipped') = 0
--remove trailing comma from parameter list
UPDATE #FmtOnlyExecutes
SET Parameters = LEFT(Parameters, LEN(Parameters) - 1)
WHERE RIGHT(Parameters, 1) = N','
--execute procs
DECLARE @.ExecuteStatement nvarchar(4000)
DECLARE ExecuteStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT RTRIM(ProcedureName) +
N' ' +
Parameters
FROM #FmtOnlyExecutes
ORDER BY ProcedureName
OPEN ExecuteStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM ExecuteStatements INTO @.ExecuteStatement
IF @.@.FETCH_STATUS = -1 BREAK
RAISERROR (@.ExecuteStatement, 0, 1) WITH NOWAIT
EXEC(@.ExecuteStatement)
END
CLOSE ExecuteStatements
DEALLOCATE ExecuteStatements
DROP TABLE #FmtOnlyExecutes
GO
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Just D." <no@.spam.please> wrote in message
news:zwthe.10252$D91.4971@.fed1read01...
> It's known that the SQL Server doesn't check the SPs and views when we
> modify the tables and if we try to use these old SPs after we applied any
> modifications like column names to the tables we'll get very serious
> problems.
> Is it possible to check all SPs and View from one script, like open each
> one, check if it's correct (maybe try to save?) and if it's a problem then
> save the name of this wrong SP to some table or just provide a list with
> these names?
> Dmitri.
>|||Jens,
My idea was to open the SP for modification but actually do not do anything
bad, but to let the Sql Server know that it was modified we can add a space
in the very end for example and then try to save it. If it fails then
report/add a name to some table, that this SP is wrong. That's easier that
you're suggesting.
I'm just wondering if anybody is already having this script?
Dmitri.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OdeAyxMWFHA.3636@.TK2MSFTNGP14.phx.gbl...
> Oh, thats a good one, the only thing that come in mind would be to figure
> out all Procedures (in all sysobjects) and to identify for every single
> procedure the parameters in syscolumns. Then you have to create a
> SQLStatement with the paramters and some dummy values within an automatic
> Rollback transaction and query after execution for the @.@.error to identify
> wheter it was executed valid or not.
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Just D." <no@.spam.please> schrieb im Newsbeitrag
> news:zwthe.10252$D91.4971@.fed1read01...
>