getting modified tables list - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

getting modified tables list

Author

Topic

safecoder@gmail.com
Starting Member

22 Posts

Posted - 2006-08-02 : 00:42:26

all -

i need some help with getting the list of modified tables from sysobjects (or from somewhere)...I need to know all the list of tables that were modified (only schema changes, not the date changes) in the last two weeks..please reply if u guys find anything helpful...

thanks,

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-02 : 02:16:44

I dont think, that modified datetime of the any changes(Schema) to the table is been saved anywhere.

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-02 : 03:38:26

SELECT * FROM sysobjects

If you look at the crdate, this column is updated whenever a change is made to the database.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-08-02 : 03:54:22

"this column is updated whenever a change is made to the database"

I think its only modified when an object is created, not if it is modified.

One option would be to recover the database (to a TEMP database) from a backup of two weeks ago, and then compare the two (e.g. using Red-Gate's DB Comparison tool, which I think has a trail-period version)

If can show you a "DIFF" of structural changes to tables, as well as data (in your own tables, as well as sysobjects etc.)

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-02 : 04:31:34

Yes, you are right.

When altering from QA, the sysobjects is not updated.
When altering from EM, the sysobjects is updated.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-02 : 04:45:04

Kristen, there is a quirk!

Begin with executing DBCC LOG (yourdatabase, 1)
Look at the description column and search for your table. Remember the LSN to the left.

Then execute DBCC LOG (yourdatabase, 3). Search for the previously remembered LSN.

Now look at the Begin Time column.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-02 : 05:26:35

Or, use this very simple and effective query

SELECT		so.name ObjectName,
		so.type ObjectType,
		max(convert(datetime, l.[Begin Time])) LogTime
FROM		::fn_dblog (DEFAULT, DEFAULT) l
inner join	sysobjects so on so.name = l.[transaction name]
--where		so.type = 'u'
group by	so.name,
		so.type
order by	so.name,
		so.type

Changing INNER JOIN to RIGHT JOIN gives you all objects in the database.

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-02 : 05:55:11

Cool Query

But will query returns any object which are modified in the current week
or its just limited to the current date??

quote:

::fn_dblog (DEFAULT, DEFAULT) l

and also what does this code means i tried to look in bol didnt find anything

Chirag

safecoder@gmail.com
Starting Member

22 Posts

Posted - 2006-08-02 : 12:28:45

Thank you all for all your replies..But, i thinki don't have permissions to look @ the Databaselog or even execute fn_dblog

Let me know if there are any other workarounds..

Thanks a ton again for all the help!

quote:
Originally posted by Peso

Or, use this very simple and effective query
SELECT so.name ObjectName,
so.type ObjectType,
max(convert(datetime, l.[Begin Time])) LogTime
FROM ::fn_dblog (DEFAULT, DEFAULT) l
inner join sysobjects so on so.name = l.[transaction name]
--where so.type = 'u'
group by so.name,
so.type
order by so.name,
so.type
Changing INNER JOIN to RIGHT JOIN gives you all objects in the database.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources