September 8, 2011 at 11:48 am
Is there any way we can detect what columns were added most recently to a table in sql server.
thanks
September 8, 2011 at 11:52 am
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
September 8, 2011 at 12:16 pm
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
September 8, 2011 at 3:45 pm
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://www.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://www.sqlservercentral.com/search/?q=compare+tables&t=s
September 8, 2011 at 4:14 pm
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);
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply