Exists some table that the alters of SP are registered?

Question

Exists some table that the alters of SP are registered?

Hector Angello Silva Carmona

SSCrazy

Points: 2720
More actions
January 19, 2005 at 6:10 am

#162269

Friends exists some table that the alters of Store procedure are registered?
in the SysOBject only the date of creation is registered but the date of update is not.. or exists a way to realize that a Sp have been altered without to considerate the TLog ?
thanks
sorry i hope that my english will be understood.

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

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

If a stored procedure is changed via ALTER and not via DROP and CREATE you can not rely on the crdate in sysobjects. In that case i think you have to use some Third-party tool.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65143 More actions · Answer 2

This question was asked on a thread a while ago and I think someone's solution was to only DROP / CREATE procedures and to snapshot the creation dates daily (or whatever period is required) and compare to previous snapshot.

Far away is close at hand in the images of elsewhere.
Anon.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Yes, this is also a FAQ! But unless I am mistaken, you need to take care for permissions when you DROP and CREATE. I don't think you need to do this when you ALTER.

Hey Dave, still missing your explanation on this sitting on the fence thing. Honestly, I don't understand this!

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65143 More actions · Answer 4

Yes, however ALTER will create a second entry in sysobjects with an increased value of base_schema_ver.

DROP will remove all these entries and another CREATE will set it back to 0.

using the combination of name,crdate and base_schema_ver could be enough

Far away is close at hand in the images of elsewhere.
Anon.

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 5

mmmm sorry but i have done some prooves and it doesnt happen how you say.

I have altered a table by enterprise manager and then i look into the SysObject

and there is no a second entry only one, Why? does my sqlserver need a special configuration? thanks

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 6

I have some prooves and in my table sysobject i cant see the second entry generated but the field base_schema-_ver changed, i try include a trigger in the SysObject so create a log table that will be inserted when the SysObject changes, but appears a message of error , you can see it below, one question, the field base_schema_ver is used in another table ?

Thanks............

Server: Msg 229, Level 14, State 5, Procedure CP_TrigerSysObject, Line 11

CREATE TRIGGER permission denied on object 'sysobjects', database 'Terrabd', owner 'dbo'.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Creating a trigger on that system table would be way tooo easy. You have found out already that you can't do so. And there is no way I know of.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

mark baekdal-145375 SSCrazy Points: 2200 More actions · Answer 8

mark baekdal-145375

SSCrazy

Points: 2200

January 20, 2005 at 12:29 am

#538229

Heard of DB Ghost?

http://www.dbghost.com

David Burrows SSC Guru Points: 65143 More actions · Answer 9

I have altered a table by enterprise manager and then i look into the SysObject and there is no a second entry only one, Why?

The base_schema_ver change only applies to stored procedures, as you found out the same does not apply to tables.

If you want to monitor changes to tables the regular snapshot and comparison is one way to do it.

Far away is close at hand in the images of elsewhere.
Anon.

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 10

the base_Schema_ver is applies to tables too, but the second entry doent appear into SysObject , so i dont have the date of the change just i know that the table has changed .. well i have to create a check process only. thanks

David Burrows SSC Guru Points: 65143 More actions · Answer 11

If you want to capture the date/time of table changes then

create table changelog (table_name sysname, base_schema_ver int, change_date datetime)

insert into changelog (table_name, base_schema_ver, change_date)

select [name],base_schema_ver,GETDATE()

from sysobjects

where type = 'u'

Then at periodic intervals (hour, day, week, month etc)

insert into changelog (table_name, base_schema_ver, change_date)

select o.[name],o.base_schema_ver,GETDATE()

from sysobjects o

where o.type = 'u'

and not exists(select 1

from changelog l

where l.[name] = o.[name]

and l.base_schema_ver = o.base_schema_ver)

Far away is close at hand in the images of elsewhere.
Anon.