October 9, 2009 at 6:54 am
When deciding to recompile a slow SP that we have on our development server I came across an article that describes one way to recompile all SPs and Views:
I changed it slightly, with a Try/Catch as it would stop as soon as it came across a View that had errors within it. I found this interesting, as it helps to find any View we have missed when changes have been made to the DB. I had always assumed that sp_recompile and sp_refreshview set something that forced a recompilation when the next request to run the code was instigated, but apparently not (in the case of sp_refreshview) and quite useful it would appear to be too.
I have attached a simple example of what happens when a table has been changed that a View refers to when sp_refreshview is executed.
However, sp_recompile doesn't care about such things, so I can't use the same prinicple the check SPs. Is there another method I can use to check our SPs en masse? I haven't been able to find anything to use and would appreciate some pointers.
Thanks,
BrainDonor.
October 12, 2009 at 1:47 pm
You could write some SMO code that would basically iterate through all the stored procedures in your database, extract the underlying TSQL for each and try to compile it. The code might look something like this -- NOTE: some of the calls aren't correct -- you'll have to tweak.
// Do for Each Stored Procedure In Database...
foreach (StoredProcedure sp in . . .)
{
// Get the T-SQL for this Procedure
string MySQL = sp.GetText();
// Try to Compile It
try
{
MyConnection.ExecuteNonQuery(MySQL);
}
catch(Exception e)
{
// Handle Any Errors Here
}
}
October 12, 2009 at 3:14 pm
Try this:
DECLARE
@sql VARCHAR(MAX),
@Text VARCHAR(MAX),
@ProcName VARCHAR(500),
@ProcName1 VARCHAR(500)
DECLARE @T TABLE (
ProcName VARCHAR(200),
sql VARCHAR(MAX),
ErrorMessage VARCHAR(4000))
DECLARE c CURSOR
FOR SELECT
name,
definition
FROM
sys.all_objects
INNER JOIN sys.sql_modules
ON all_objects.object_id = sql_modules.object_id
WHERE
type IN ('p', 'tf', 'if')
AND name NOT LIKE 'dt_%'
AND name NOT LIKE 'sys_%'
OPEN C
FETCH NEXT FROM c INTO @ProcName, @Text
WHILE @@FETCH_STATUS = 0
BEGIN
SET @text = REPLACE(@text, @ProcName, @ProcName + 'CreateTest') -- change proc name
BEGIN TRY
EXEC (@text) -- try to create the proc
INSERT
@T
VALUES
(
@ProcName,
@text,
ERROR_MESSAGE()) -- record procs that couldn't be created
END TRY
BEGIN CATCH
INSERT
@T
VALUES
(
@ProcName,
@text,
ERROR_MESSAGE()) -- record procs that couldn't be created
END CATCH
IF EXISTS ( SELECT
*
FROM
sys.all_objects
WHERE
name LIKE '%' + @procname + 'createtest'
AND type = 'p' )
BEGIN
SET @sql = 'drop procedure '
+ (SELECT
name
FROM
sys.all_objects
WHERE
name LIKE '%' + @procname + 'createtest'
AND type = 'p')
EXEC (@sql)
END
IF EXISTS ( SELECT
*
FROM
sys.all_objects
WHERE
name LIKE '%' + @procname + 'createtest'
AND type IN ('if', 'tf') )
BEGIN
SET @sql = 'drop function '
+ (SELECT
name
FROM
sys.all_objects
WHERE
name LIKE '%' + @procname + 'createtest'
AND type IN ('if', 'tf'))
EXEC (@sql)
END
FETCH NEXT FROM c INTO @ProcName, @Text
END
CLOSE c
DEALLOCATE c
SELECT
*
FROM
@T
go
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 12, 2009 at 9:18 pm
Keep in mind this may not catch errors buried in various IF statements, especially if they happen to be buried several layers deep.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 13, 2009 at 2:26 am
Hi GSquared,
Thanks for the code, it works fine - I appreciate the time taken to work it out. Interestingly, it detects changes to column names (which is my main interest) but not dropped tables. I guess that it doesn't validate the table names on the assumption that they may be created prior to the SP executing. Yet sp_refreshview does complain.
John_jakob, thanks for the reply but I prefer to use SQL wherever possible - just personal preference. I spent many years with VB and DMO and still wake up screaming.
October 13, 2009 at 8:25 am
Yeah, this script is just a starting point. But it will find the more eggregious errors in many cases.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 13, 2009 at 1:40 pm
I wrote this a while back. Some said it did not work for them, but I don't know why. I am guessing user error.
Either way. It worked fine for me. I used it to see what sprocs would not compile in a SQL 2008 db (I restored a backup of a SQL 2000 version to an SQL 2008 server).
Here is the link.
http://www.sqlservercentral.com/scripts/Syntax+Check/66363/
Hope it works for you.
October 19, 2009 at 4:20 am
Thanks to you both, GSquared and Vaccano. I've played with both routines for a while and they both do what I need. Interestingly they have both rejected a couple of old routines that use double-quotes in a couple of places; whereas SSMS lets me ALTER or CREATE these routines without complaint. I've never used double-quotes in SQL, so I had no idea they could be used anyway.
The list of things I don't know about SQL Server is now a little shorter. Still probably a very long list though.
BrainDonor.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply