how to find out what columns were the most recent in a table

Question

Post reply

how to find out what columns were the most recent in a table

SQLTestUser

Hall of Fame

Points: 3997
More actions
September 8, 2011 at 11:48 am

#256820

Is there any way we can detect what columns were added most recently to a table in sql server.

thanks
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 1

Generally, the higher column IDs are the ones added last, but that can be wrong if you're using the table designer GUI and tables are being rebuilt instead of altered.

I'm assuming you don't have DDL logging or source control in use for your databases, right? (If so, it would be easy to tell which things were modified in which sequence.)

Can you restore prior versions of the database from backups and check that way?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 2

DDL loggin was not created, and i cannot even restore a previous version. i do have a database is a diff server that has a backup copy of the database with changed columns, i will have to compare the backup and the new version. any other ideas, i just have to compare four tables , is there a script that can compare two tables on a linked server

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 3

My company, Red Gate Software, sells a product that does comparisons. It will give you the schema differences. They have a product for data differences as well.

Are you looking for both? Schema is easier. This script (from WayneS: http://qa.sqlservercentral.com/Forums/Topic1072025-392-1.aspx) should help

SELECT TableName = st.NAME,



       ColumnName = sc.NAME,

       sc.column_id

  FROM [db2].sys.tables st

       JOIN [db2].sys.columns sc

         ON st.object_id = sc.OBJECT_ID

EXCEPT

SELECT TableName = st.NAME,

       ColumnName = sc.NAME,

       sc.column_id

  FROM [db1].sys.tables st

       JOIN [db1].sys.columns sc

         ON st.object_id = sc.OBJECT_ID

ORDER BY TableName, column_id

If you want data, tablediff from Microsoft can do it. Or there are some script here: http://qa.sqlservercentral.com/search/?q=compare+tables&t=s

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

mister.magoo SSC-Forever Points: 47068 More actions · Answer 4

IF the server has not been restarted since the changes, you can try the Schema Changes History report available from the reports menu off the database node in SSMS.

It's not the most reliable source due to the fact it only works since restart and will fall over quite often, but if it works and your changes are there, it is certainly simple to use.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);