Very Computer

by **Amos Som** » Mon, 10 Feb 2003 03:42:41

I have found sp_refreshview quite useful in helping determine which of my
views might be broke because of schema changes etc. Is there something
equivalent for stored procedures and triggers? I would like some way to
proactive about validating my stored procs and triggers instead of learning
about problems when my app runs. Is anything available that does this?

Thanks very much.

by **Dan Guzma** » Mon, 10 Feb 2003 05:36:06

You can execute procedures with SET FMTONLY ON to identify those with
potentially problems. The script below (SQL 2000) will generate a
script to do this for all user stored procedures in the current
database. I'm not aware of a method to identify trigger errors, though.

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
GO

--remove trailing comma from parameter list
UPDATE #FmtOnlyExecutes
SET Parameters = LEFT(Parameters, LEN(Parameters) - 1)
WHERE RIGHT(Parameters, 1) = N','
GO

--generate EXEC script
SELECT RTRIM(ProcedureName) +
N' ' +
Parameters + N'
GO' --GO must be on separate line
FROM #FmtOnlyExecutes
ORDER BY ProcedureName
GO

DROP TABLE #FmtOnlyExecutes
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

Quote:> I have found sp_refreshview quite useful in helping determine which of
my
> views might be broke because of schema changes etc. Is there
something
> equivalent for stored procedures and triggers? I would like some way
to
> proactive about validating my stored procs and triggers instead of
learning
> about problems when my app runs. Is anything available that does
this?

> Thanks very much.

by **Amos Som** » Tue, 11 Feb 2003 22:53:23

Dan,

Thanks for the code, but I'm not certain what it does, or whether it does
what I want. I created a test stored procedure that Selects against a table
that doesn't exist. The code below didn't detect any problems with the
stored proc.

Amos.

> You can execute procedures with SET FMTONLY ON to identify those with
> potentially problems. The script below (SQL 2000) will generate a
> script to do this for all user stored procedures in the current
> database. I'm not aware of a method to identify trigger errors, though.

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

> --remove trailing comma from parameter list
> UPDATE #FmtOnlyExecutes
> SET Parameters = LEFT(Parameters, LEN(Parameters) - 1)
> WHERE RIGHT(Parameters, 1) = N','
> GO

> --generate EXEC script
> SELECT RTRIM(ProcedureName) +
> N' ' +
> Parameters + N'
> GO' --GO must be on separate line
> FROM #FmtOnlyExecutes
> ORDER BY ProcedureName
> GO

> DROP TABLE #FmtOnlyExecutes
> GO

> --
> Hope this helps.

> Dan Guzman
> SQL Server MVP

> -----------------------
> SQL FAQ links (courtesy Neil Pike):

> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> -----------------------

> > I have found sp_refreshview quite useful in helping determine which of
> my
> > views might be broke because of schema changes etc. Is there
> something
> > equivalent for stored procedures and triggers? I would like some way
> to
> > proactive about validating my stored procs and triggers instead of
> learning
> > about problems when my app runs. Is anything available that does
> this?

> > Thanks very much.

by **BP Margoli** » Wed, 12 Feb 2003 04:15:27

Amos,

Check out the section "Deferred Name Resolution and Compilation" in the SQL
Server Books Online.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

> Dan,

> Thanks for the code, but I'm not certain what it does, or whether it does
> what I want. I created a test stored procedure that Selects against a
table
> that doesn't exist. The code below didn't detect any problems with the
> stored proc.

> Amos.

> > You can execute procedures with SET FMTONLY ON to identify those with
> > potentially problems. The script below (SQL 2000) will generate a
> > script to do this for all user stored procedures in the current
> > database. I'm not aware of a method to identify trigger errors, though.

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

> > --remove trailing comma from parameter list
> > UPDATE #FmtOnlyExecutes
> > SET Parameters = LEFT(Parameters, LEN(Parameters) - 1)
> > WHERE RIGHT(Parameters, 1) = N','
> > GO

> > --generate EXEC script
> > SELECT RTRIM(ProcedureName) +
> > N' ' +
> > Parameters + N'
> > GO' --GO must be on separate line
> > FROM #FmtOnlyExecutes
> > ORDER BY ProcedureName
> > GO

> > DROP TABLE #FmtOnlyExecutes
> > GO

> > --
> > Hope this helps.

> > Dan Guzman
> > SQL Server MVP

> > -----------------------
> > SQL FAQ links (courtesy Neil Pike):

> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > -----------------------

> > > I have found sp_refreshview quite useful in helping determine which of
> > my
> > > views might be broke because of schema changes etc. Is there
> > something
> > > equivalent for stored procedures and triggers? I would like some way
> > to
> > > proactive about validating my stored procs and triggers instead of
> > learning
> > > about problems when my app runs. Is anything available that does
> > this?

> > > Thanks very much.

Very Computer

sp_refreshviews for Stored Procs and Triggers?

sp_refreshviews for Stored Procs and Triggers?

sp_refreshviews for Stored Procs and Triggers?

sp_refreshviews for Stored Procs and Triggers?

sp_refreshviews for Stored Procs and Triggers?