June 19, 2008 at 3:53 am
I need to track the informaton related to the change in table,view,
procedure and function's definition. I mean to say the information like
modification date,what is modified, if possible then ip address of the system from which object is modified etc.
I have tried with sysobjects and some system tables and view but they are having creation date only. If there is any third party tool that u know,please give me the link to download..
Please note that i m using SQL server 2000
Kindly help me.................
Thanks
Shailesh
June 19, 2008 at 4:38 am
enabling C2 auditing may help you with this but i would research doing this properly before implementing it can lead to numerous problems, poor performance being one, as well as the headache of storing all the data it generates. There are some articles on this site that would be worth reading to further you knowledge.
Gethyn Elliswww.gethynellis.com
June 19, 2008 at 5:19 am
[font="Tahoma"]Hi Shailesh,
If you are working with SQL Server 2005 then you can make use of DDL triggers to capture the details you have mentioned above. This will be simple and will fire only when there is any modification to the table schema!
Refer DDL triggers in BOL for more[/font]
Regards..Vidhya Sagar
SQL-Articles
June 19, 2008 at 5:28 am
Thank u vidhya,
But my problem is that I have SQL server 2000. And I have to do in this.
Thank u
Shailesh
June 19, 2008 at 7:57 am
Try Server Side trace for Capturing any specific DB Activity or Overall.
Before doing that, Check for available Space on your BOX, because these traces can get HUGE.
Now Do you want to track the Data Itself or Just the Table/DB Size..
Because Tracking Table / DB Size Tracking can be very simple Procedure, but Capturing Data, is using Traces or Triggers.
Check this link for more info: http://weblogs.sqlteam.com/brettk/archive/2006/08/10/11126.aspx
Maninder
www.dbanation.com
June 19, 2008 at 8:17 am
Unfortunately there's no 5-minute way to do this in SQL 2000. One approach would be to script the schema into individual objects (e.g. using a tool like ApexSQL Script or SYDI-SQL[/url]) and put it into some kind of source control. Then set up the tool to automate scripting on a regular interval and check out\in the scripts. You can then use the source control to generate reports on which objects have changed over time. The advantage to doing this is that you also have a copy of the schema available that you can expose to developers so they know exactly what your environment (I assume production) looks like.
Another approach would be to run a server side trace with a filter on specific keywords in the query text like "create", "drop", "alter", etc.. You can periodically audit the trace manually to see if changes have occurred or set up some kind of automated monitoring to alert you as soon as a change is detected.
June 20, 2008 at 12:32 am
Hi,
Do ur developer change the views,tables,procs etcwithout nowing u...
....if no then there is a tool called vss in which u can compare each and every it will show u what has been changed in procs,tables etc....
....make ur security for live server really tight dnt give ur live server access to any 1 excpt to dba any changes any where will be done from ur side ....and make sure that query anlyzer for live databse shud not be accessed by ur developer team.
Security policy shud be implement and it is ur rspnbility.
June 20, 2008 at 3:55 am
Thank u all for the valuable suggestions...
I got one tool that will compare the two databases objects and show u the new changes made.I think it will be bit helpful for me
If any body is having some other option, please tell me.
Thanks
Shailesh
June 21, 2008 at 10:16 am
SQL Server 2000 unfortunately does not help very much with this kind of information, ALTER will change the content but not the creation date, (2005 looks to be much better). You can fairly easily track that changes have been made, just not the EXACT date they were changed.
What you can do is save a copy of your sysobjects (and jobs and dts) information to other tables. Then after a period of time has gone by, you can save another copy. Anything that shows up in the second copy and was not there the first copy was created sometime between those two copies/dates. Anything that vanishes between the two was deleted between those two copies/dates.
Changes are the fun things to find. If a DROP/CREATE was used, the create date will change. If an ALTER was used, you can use the [schema_ver] column, if the value changes that object was changed. [schema_ver] usually changes in values of 16 and multiple changes may have happened since your first copy.
Jobs have a change date, so they are easier to track. DTS packages create a new row (same GUID) when a newer version is saved.
[schema_ver] is ALWAYS zero in 2005, so this will only work with 2000.
This will tell you "when" it was changed, but not what those changes were. If you need to know that you will also have to save syscomments, etc. Sometimes it is just easier to script the database objects with DMO/SMO and then you can compare the files to see exactly what was changed.
Search Lazy_DBA for schema_ver, there is a pretty good example there.
June 27, 2008 at 7:40 am
a possible scenario:
for db tables, dump all tables from "updated" db:
SELECT o.name Table_Name, c.name Column_Name, t.name DataType, c.Length, CASE WHEN c.isnullable=1 THEN 'Yes' ELSE 'No' END AS 'Nullable'
FROM sysobjects o
INNER JOIN syscolumns c ON (o.id=c.id and o.type='U' and o.name not like 'dt%')
Inner Join systypes t ON (c.xtype=t.xtype)
order by o.name, c.name
do same from "not update db"
cross reference joins.
Should emerge fields, etc, changed....
similar tech I guess can be used to compare stored... or more easier wih a grep or file-file compare
June 27, 2008 at 8:11 am
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply