Prev: help!!
Next: help on if logic
From: Michael C on 22 Jul 2008 03:37
I remember once apon a time I had some code that would fix up the sysdepends
table. Basically it would go through every stored proc/view/function etc,
get their help text and execute it (with an alter statement). I did this in
C# but the code is now the property of my former employer. Before I redo all
that hard work does anyone have a purely sql script that does basically the
same thing?


From: "Ami Levin" ami at dbsophic dot on 22 Jul 2008 04:50
Hi Michael,

Red gate has a product that addresses this issue:
http://www.red-gate.com/products/sql_packager/technical_papers/damaged_sysdepends.htm
--
Ami Levin
SQL Server MVP
http://www.dbsophic.com
Database performance is the most significant driver in maintaining data and
service availability.

"Michael C" <mike(a)nospam.com> wrote in message
news:%23ZbFM386IHA.5012(a)TK2MSFTNGP02.phx.gbl...
>I remember once apon a time I had some code that would fix up the
>sysdepends table. Basically it would go through every stored
>proc/view/function etc, get their help text and execute it (with an alter
>statement). I did this in C# but the code is now the property of my former
>employer. Before I redo all that hard work does anyone have a purely sql
>script that does basically the same thing?
>

From: Bob on 22 Jul 2008 05:04
In SQL2005 SP2 +, you can do this with sp_refreshview and sp_refreshsqlmodule

-- Wrap these in a cursor for example
-- Run sp_refreshsqlmodule for ( stored procedure, scalar function,
table-valued-function, inline table-valued function ), SQL only
SELECT 'EXEC sp_refreshsqlmodule ' + QUOTENAME( SCHEMA_NAME ( schema_id ) +
'.' + name ) AS refresh_sql
FROM sys.objects
WHERE type In ( 'P', 'FN', 'TF', 'IF' )

SELECT 'EXEC sp_refreshview ' + QUOTENAME( SCHEMA_NAME ( schema_id ) + '.' +
name ) AS refresh_sql
FROM sys.objects
WHERE type = 'V'
AND OBJECTPROPERTY( object_id, 'IsSchemaBound' ) = 0


-- Demo

USE tempdb
GO

DROP PROC usp_test1
DROP PROC usp_test2
GO


CREATE PROC usp_test2

AS

EXEC usp_test1
GO

-- Should get warning; check dependencies
EXEC sp_depends 'usp_test2'
GO

CREATE PROC usp_test1

AS

SELECT 1 x
GO

-- Should still be empty
EXEC sp_depends 'usp_test2'
GO

-- Refresh now
EXEC sp_refreshsqlmodule usp_test2
GO

-- Should get results
EXEC sp_depends 'usp_test2'
GO

HTH
wBob

"Michael C" wrote:

> I remember once apon a time I had some code that would fix up the sysdepends
> table. Basically it would go through every stored proc/view/function etc,
> get their help text and execute it (with an alter statement). I did this in
> C# but the code is now the property of my former employer. Before I redo all
> that hard work does anyone have a purely sql script that does basically the
> same thing?
>
>
>
From: Michael C on 24 Jul 2008 17:50
"Bob" <Bob(a)discussions.microsoft.com> wrote in message
news:33FB8CBE-BAFE-443C-AD91-BB6E47605C8D(a)microsoft.com...
> In SQL2005 SP2 +, you can do this with sp_refreshview and
> sp_refreshsqlmodule

Thanks guys, I will have a look at both solutions (I'm a bit distracted at
the moment).

Cheers,
Michael


 | 
Pages: 1
Prev: help!!
Next: help on if logic