How to check latest SP is on server

Question

Post reply

How to check latest SP is on server

Staggerlee

SSCertifiable

Points: 5272
More actions
October 14, 2014 at 4:23 am

#291706

Hey all,
Im trying to build out a system for making sure all our servers meet the same build. Is there a way via T-SQL to check or read information from a stored procedure? Ie, could I add a comment with a build number in it?
I know i could check with PBM if the SP is there.. but when we update it, I want to know if a server is running the old version and needs updating?
Could it be done with SSDT maybe?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

mssqlsrv SSCrazy Points: 2375 More actions · Answer 1

you can use below query.

select serverproperty('productlevel')as sql_ervice_pack,serverproperty

('productversion') as Sql_version

Staggerlee SSCertifiable Points: 5272 More actions · Answer 2

Hi,

Thanks for the reply, but i think i must have poorly explained my problem.

an example would be,

you want Brent Ozar's SP_Blitz on each machine and you want to track what version is currently installed.

the only way i can think of checking that, is to read the T-SQL within the create proc statement, but im not sure you can do that. otherwise ill have to create a table and when we run the install update the table with the version.

Im happy to do that. but was hoping that there was a nicer way to do it.

Cheers

S

Staggerlee SSCertifiable Points: 5272 More actions · Answer 3

Little update,

so sp_helptext can return infor on a stored proc, which looks like a way to it. link below. Id be interested to hear how anyone does deployments if they have the time to solve this problem (Though i cant see much wrong with just dropping and creating no matter what situation. but i cant track the current build version..)

http://msdn.microsoft.com/en-gb/library/ms345443.aspx

Nadrek SSC-Insane Points: 20039 More actions · Answer 4

Start by putting a standardized, unique comment at the start of each stored procedure, listing the version.

Then you can use something like:

SELECT

CASE

WHEN PATINDEX('%UniqueVersion:%',definition) > 0 THEN SUBSTRING(definition, PATINDEX('%UniqueVersion:%',definition)+15, 12)

ELSE 'UNKNOWN'

END AS MyVersion

,* from sys.sql_modules

to find it.

Staggerlee SSCertifiable Points: 5272 More actions · Answer 5

Thanks Nadrek!

so is this something you do? im interested to see if this is a good idea. or just a nasty workaround..

Regards

S

Nadrek SSC-Insane Points: 20039 More actions · Answer 6

No; I just run a redeploy anytime I question whether an SP is up to date.

The only other real way to do it is simply compare the entire definition column of the sys.sql_modules table to a known good reference; however, SQL Server tends to be sharply limited in options once you get past 8000 bytes of data. HASHBYTES, for example, only hashes the first 8000 characters, so that's nearly worthless for stored procedure checks.

Staggerlee SSCertifiable Points: 5272 More actions · Answer 7

thanks Nadrek,

I do think drop create is the simplist solution, and the other info is great to..

ScottPletcher SSC Guru Points: 100947 More actions · Answer 8

Staggerlee (10/14/2014)
Is there a way via T-SQL to check or read information from a stored procedure? Ie, could I add a comment with a build number in it?

I suggest extended properties. Just make sure you never DROP the proc, only ALTER it, as drop will also drop extended properties :pinch:.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

sqldriver SSChampion Points: 10440 More actions · Answer 9

One thing you could do is hash the sp text and compare the last date modified. So if the modified date is newer and the hashes don't match, then it's potentially out of date.

SELECT DISTINCT

o.name AS Object_Name ,

o.type_desc ,

HASHBYTES('MD5', CONVERT(VARCHAR(8000), m.definition)) ,

o.modify_date ,

*

FROM sys.sql_modules m

INNER JOIN sys.objects o ON m.object_id = o.object_id

WHERE o.is_ms_shipped = 0

Staggerlee SSCertifiable Points: 5272 More actions · Answer 10

hey all thanks for the ideas.

theres certainly some fun ideas to solve the issue. im looking at adding extended properties to everything at the moment and then use PBM to do checks.

s

ScottPletcher SSC Guru Points: 100947 More actions · Answer 11

You could even DROP and (re)CREATE the procedure as long as you re-added the extended procedure with the version number after the CREATE.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Nadrek SSC-Insane Points: 20039 More actions · Answer 12

sqldriver (10/15/2014)
One thing you could do is hash the sp text and compare the last date modified. So if the modified date is newer and the hashes don't match, then it's potentially out of date.

HASHBYTES only operates on the first 8000 bytes, and in many environments it's very common for stored procedures to be longer than that. Therefore, if the hashes don't match, it's different, but if the hashes do match and the length is over 8000 bytes, you still don't know anything definitive until you compare the entire definition.