CREATE PROCEDURE sp_AdminRecompileStoredProc
AS
BEGIN 

 declare @continue	integer,
	 @TableCounter	integer,
	 @string	varchar(80),
	 @TableName	varchar(50),
   	 @SQL		varchar (255)

 set nocount on 

 declare curTable cursor
 for SELECT name
	FROM 		sysobjects
	WHERE 		type = 'U'
	order by	name
	for read only

 -- open cursor
 open curTable

 select 	
  @continue	= 1,
  @TableCounter	= 0,
  @string = ""

 while 	(@continue = 1)
  begin
   fetch next from curTable
	into @TableName

   if (@@FETCH_STATUS <> 0)
     begin
     --fetch failed; end loop
     select @continue = 0
     break
     end	--if fetchstatus <> 0

   else	-- continue next table
     begin
     --successful fetch
     select @TableCounter = @TableCounter + 1
     select @SQL = "sp_recompile " + @TableName
     exec (@SQL)
     end	-- else successful fetch
 end --while (@continue = 1)

 -- final cleanup
 close curTable
 deallocate curTable

 select "Total Number of Tables:" = @TableCounter

END

go

CREATE PROCEDURE spDeleteRows
/*
Recursive row delete procedure

It deletes all rows in the table specified that conform to the criteria selected, 
while also deleting any child/grandchild records and so on. This is designed to
do the same sort of thing as Access's cascade delete function. It first reads
the sysforeignkeys table to find any child tables, then deletes the soon-to-be
orphan records from them using recursive calls to this procedure. Once all the
child records are gone, the rows are deleted from the selected table. It is 
designed at this time to be run at the command line, but could also be used
in code, but the printed output will not be available.
*/

(
@cTableName varchar(50), /* name of table were rows are to be deleted */
@cCriteria nvarchar(1000), /* criteria used to delete the rows requried */
@iRowsAffected int OUTPUT /* number of rows affected by the delete */
)
AS
SET NOCOUNT ON

DECLARE
@cTab varchar(255), /* name of the child table */
@cCol varchar(255), /* name of the linking field on the child table */
@cRefTab varchar(255), /* name of the parent table */
@cRefCol varchar(255), /* name of the linking field in the parent table */
@cFKName varchar(255), /* name of the foreign key */
@cSQL nvarchar(1000), /* string passed to the sp_ExecuteSQL procedure */
@cChildCriteria nvarchar(1000), /* critera to delete child table records */
@iChildRows int /* number of rows delete from the child table */

/* declare the cursor containing the foreign key constraint information */
DECLARE cFKey CURSOR LOCAL FOR
SELECT SO1.name AS Tab,
       SC1.name AS Col,
	   SO2.name AS RefTab,
	   SC2.name As RefCol,
	   FO.name AS FKName
FROM dbo.sysforeignkeys FK
INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id AND FK.fkey = SC1.colid
INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id AND FK.rkey = SC2.colid
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id	   
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id
INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id
WHERE SO2.Name = @cTableName

OPEN cFKey
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
WHILE @@FETCH_STATUS = 0
  BEGIN
  /* build the criteria to delete rows from the child table. As it uses the 
     criteria passed to this procedure, it gets progressively larger with
	 recursive calls */
  SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' +
     @cRefTab + '] WHERE ' & @cCriteria + ')'
  print 'Deleting records from table ' + @cTab
  /* call this procedure to delete the child rows */
  EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT
  FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
  END
Close cFKey
DeAllocate cFKey
/* finally delete the rows from this table */
SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria
EXEC sp_ExecuteSQL @cSQL
print 'DELETED ' + CONVERT(varchar, @@ROWCOUNT) + ' records from ' @cTableName