December 22, 2002 at 9:39 pm
my table
tablename A:
id int
name varchar(20)
my changed table
tablename A:
int id
name varchar(20)
detail varchar(50)----add one volumn
or
id int
username varchar(20)---'username 'replace 'name'
how can i know table is changed.
thanks guys.
December 23, 2002 at 11:32 am
If your question is how you know that table structure has changes since the last time you checked, you could use sysobjects.schema_ver column value:
SELECT schema_ver FROM sysobjects WHERE name = 'tablename'
List of columns could be read from the syscolumns table. oin it with sysobjects on id column.
I hope this is what you meant...
Michael
December 23, 2002 at 11:35 am
schema_ver is just a number that changes each time you make a table change. You will need to keep track of what was before you make a change in order to determine that a change has been made. Also this number get reset to 1 when you drop and recreate the table.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 23, 2002 at 12:43 pm
Greg is correct. The way I manage changes to database structure is this. I never make changes through Enterprise Manager, only through Query Analyser using SQL script file, ne file per table, SP, etc. Files are stored in VSS so I have version control.
In the same time you can monitor if changes to your structures are being made by anybody if you capture schema_ver periodically and store it in some dataabse. You can even alert yourself in case of any change. I am not familiar with specifics of your requirements, of course.
December 23, 2002 at 4:39 pm
Couple other ways. One is to audit for changes using Profiler (or a server side trace), the other is 3rd party, Lumigent has a schema monitor tool (think we have a review of it up).
Andy
December 23, 2002 at 6:30 pm
The only issue with the latter (3rd party tools) is that the transaction log has to remain instact to see this. You could use syscolumns and other systables to copy the data from and into your own table and do a compare to see if changes occurred. Or maybe using DMO get the DDL and check it once a day. Not sure how deep you want to know if it changed (default, constraints, triggers, PK-FK relations, etc) in addition to columns.
December 23, 2002 at 7:53 pm
i really appreciate your help.
thanks .
December 26, 2002 at 7:39 am
I believe another drawback to the Lumigent tool is that it only works on SQL2k installations.
December 27, 2002 at 11:10 am
Otehr option is to create an audit db, in which you keep track of any changes in the main db. Not a simple process, but in the long run it will help you a lot.
We did built an audit db (basically what Lumigent does, but customized for our own schema) In this one we know who when what changes where made, including user ID, date, old value and new value. Is does keep track of data as well as objects
December 30, 2002 at 9:35 am
To get the who changed the table, view, sp, etc. are you getting this information from the T-Log, or some other source?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply