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
---


news:zwthe.10252$D91.4971@fed1read01...
Show quoteHide quote

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.

Show quoteHide quote

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(ROUTINE_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: m***@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

news:zwthe.10252$D91.4971@fed1read01...
Show quoteHide quote

Retrieving data from multiple tables
add column
ntext and update/insert triggers
Need help with SELECT statement please.
Dynamic query