sp_recompile - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

sp_recompile

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2007-05-18 : 14:59:51

Hey, can anyone get this to work?

EXEC sp_msforeachtable('EXEC sp_recompile ?')
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-18 : 15:22:04

EXEC sp_msforeachtable 'exec sp_recompile ''?'''

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

X002548
Not Just a Number

15586 Posts

Posted - 2007-05-18 : 15:36:43

quote:
Originally posted by spirit1

EXEC sp_msforeachtable 'exec sp_recompile ''?'''

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Thanks I'll try that, but I thought I alread did this variation

This worked for you?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-18 : 15:39:02

yup.
although i have no idea why do you want to recompile your tables...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

X002548
Not Just a Number

15586 Posts

Posted - 2007-05-18 : 15:44:17

Doesn't that also recompile the procedures when this is done?

And that worked btw

What's happening, is the database structure is pretty much in flex, so when I did sp_depends on a table that got chnaged a sproc didn't show up because I believe it wasn't recompiled after the change

I though this would fix that, which when I manually recompiled that one sproc, it did.

Do I have this wrong?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-18 : 15:47:31

honestly i have no idea. i never even thought about recompiling tables.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-05-19 : 07:20:23

"What's happening, is the database structure is pretty much in flex"

Dump the cache buffers instead? Or is that a bit severe?!

Kristen

Kristen
Test

22859 Posts

Posted - 2007-05-19 : 07:21:05

By the by, sysdepends is shot in SQL 2000, so I would not trust recompiling tables to hit the relevant Sprocs. Supposedly better/fixed in SQL 2005

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2007-05-21 : 09:36:04

Interesting...so what's the answer then?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2007-05-21 : 10:19:17

Recompile the lot?

Record the average elapsed time for each Sproc, recompile the ones where this time has fallen below some "threshold"?

Kristen

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-05-22 : 11:38:14

If you are referring to "how to fix sp_depends problem", then the solution I am using (not mine, but from some forums, possibly this one), is shown below (Once everything is created, use sp_depends2):

Below should be placed in the Master database so that we can query the syscomments table more accurately:

IF EXISTS(SELECT TOP 1 * FROM SYSOBJECTS WHERE NAME = 'NUMBERS')
BEGIN
DROP TABLE dbo.Numbers
COMMIT
GO
-- Now re-create it and fill it with sequential numbers starting at 1
SELECT TOP 10000 IDENTITY(INT,1,1) AS Num
INTO dbo.Numbers
FROM master.INFORMATION_SCHEMA.COLUMNS i1
CROSS JOIN master.INFORMATION_SCHEMA.COLUMNS i2;
GO
-- Add a primary key/clustered index to the numbers table
ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Num);
GO

-- Create a procedure for querying data
CREATE Procedure sp_depends2
@sString VARCHAR(100)

AS

BEGIN
SELECT DISTINCT O.Name, O.Type
FROM
(
SELECT Id,
CAST(COALESCE(MIN(CASE WHEN sc.colId = Num - 1 THEN sc.text END), '') AS VARCHAR(8000)) +
CAST(COALESCE(MIN(CASE WHEN sc.colId = Num THEN sc.text END), '') AS VARCHAR(8000)) AS [text]
FROM syscomments SC INNER JOIN numbers N ON N.Num = SC.colid OR N.num - 1 = SC.colid
WHERE N.Num < 30
GROUP BY id, Num
) C
INNER JOIN sysobjects O ON C.id = O.Id WHERE C.TEXT LIKE '%' + @sString + '%'
END

... hope it helps ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources