Need simple solution to keep track of altered queries in script?

Question

Post reply

Need simple solution to keep track of altered queries in script?

guptaopus

SSCommitted

Points: 1534
More actions
March 2, 2008 at 5:02 am

#179533

Hi,
I store databases in the form of scripts(Generate Scripts), If i have Stored procedure which was written at beggining and I wants to change that also I need to keep tack of old SP. This is my requirement
I am following this prodedure till now
as database size increases cant search SP so if there any changes to be done in SP will include "ALter SP...." in the last whenever i need to earlier SP will search for the from downwords...
please anybody help me in keeping track on it in smarter way??

Viewing 15 posts - 1 through 15 (of 16 total)

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

You may want to check database triggers.

At SSC you can find examples and some articles regarding db audit, ...

e.g. http://www.sqlservercentral.com/articles/Auditing/62126/

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Adam Haines SSC-Insane Points: 23217 More actions · Answer 2

You can use triggers to collect data about altered DDL, as ALZDBA suggested.

My question to you is do you have all of your scripts in some sort of source control? This is the best way to determine what the version of the script is, what changed, and who changed it.

My blog: http://jahaines.blogspot.com

guptaopus SSCommitted Points: 1534 More actions · Answer 3

Hi,

Yes data is stored in TFS, Thanks a lot for suggesion.

Will get back to you if i have any doubts in this... 🙂

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

If I understand the question, you could add an extended property to identify the current version of the procedure.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jacob Luebbers SSCarpal Tunnel Points: 4834 More actions · Answer 5

You could also so something simple like script out all your procs/views/etc. on a regular basis. We do this daily into a history table and have a running version history of all changed objects going back in time. Kind of like a poor-man's version control without any "who did it" tracking... but at least you can grab the previous day's version of an object if it was created earlier than today. Quite lightweight and no need for any expensive or cumbersome 3rd party products.

Regards,

Jacob

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

It's certainly not automatic, but my first inclination is to ask...

1. You allow folks to change production stored procedures without DBA approval?

2. Are there no documentation standards? A simple note in the revision history section of a decent header tells all.

3. Is there no source control on your procs? VSS or SubVersion?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

this is also not automated - but running a "generate scripts" task once a week would give you a quick benchmark. You coud use something freeware like WinDIFF to highlight all differences.

But still - that's like using your foot to look for land mines... Very messy, and you only know once the damage has been done:)

----------------------------------------------------------------------------------
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?

guptaopus SSCommitted Points: 1534 More actions · Answer 8

Hi,

Data is in TFS which is path based Source control from microsoft, As i am administrator for this scripts I can alter scripts.

But my requirement is that i want to track change history in one place so that i can access all versions of that code.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

Well - since you have a source control/version control solution - it's now time to "incentivize" using it. Meaning - lock your developers out of prod. The only way out/on to Production is to get the new version published into TFS, and have it reviewed, and publish it to production from TFS.

That's the only way you will get your change history. TFS can't track versions of stuff that aren't published through it: it's the same as asking you to remember a conversation you didn't have.

----------------------------------------------------------------------------------
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?

guptaopus SSCommitted Points: 1534 More actions · Answer 10

guptaopus

SSCommitted

Points: 1534

March 5, 2008 at 12:51 pm

#785720

Thanks a lot will use TFS from now. 🙂

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

Sometimes, I feel pretty stupid... I'm still coming up to speed on 2005 (installed at home in December)... what in the Dickens is "TFS"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

TFS = Visual Studio Team Foundation Server. It's a bear of a product: version control/project management/project documentation all rolled into one. And all that for a bear of a price tag, too:)

http://msdn2.microsoft.com/en-us/teamsystem/aa718934.aspx

It's like VSS on steroids + Application stress tester on steroid + project on steroids = ludicrous per user CAL price. But hey - it IS rather cool though....:)

----------------------------------------------------------------------------------
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?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

Thanks, Matt. Guess I won't be buying that for my home machine in the very near future... 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 14

Why buy when you can evaluate?

http://www.microsoft.com/downloads/details.aspx?familyid=D5C12289-F4E4-49A9-9235-AB2F6D4CA097&displaylang=en

You'll need a bigger machine though....:) It takes a rather massive chunk out of your machine (it's essentially a customized sharepoint install running on top of a local SQL Server install, all using IIS to serve it up. And it likes to take over your default IIS install, so it doesn't play incredibly well with other installed products like reporting services).....

Oh yeah - it's RAM heavy, processor heavy, and process heavy....

----------------------------------------------------------------------------------
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?