Find the Date/Time a Table's Column Was Created

Find the date/time a table's column was created

There's this system table named sys.Columns that you can get columns information from it.
if you want to see columns of a particular table you can do as follows:

SELECT col.* from sys.objects obj 
inner join sys.columns col 
on obj.object_Id=col.object_Id 
and obj.Name=@tableName

Or you can get table information like this:

SELECT * FROM sys.objects WHERE Name=@tableName

but I couldn't find any information on creation date of a column.

Updated:
This might help.

How can i get Created date of a column in SQL server Table?

I don't think that information is available in any system tables or the information schema views. I would look for that information in a source control system rather than the database.

Finding column creation time in MySQL

So you can get this information for the table quite easily. You would query the information_schema for the create_time of the table.

For instance:

SELECT create_time FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'your_schema'
  AND table_name = 'your_table'

Check here for more information:
https://dev.mysql.com/doc/refman/5.7/en/tables-table.html

For a specific column this is not as simple because none of the system tables (that is, nothing in the INFORMATION_SCHEMA database) exist that has that kind of information recorded anywhere. In other words, there is no native mechanism to put any timestamps on column changes.

Any time that:

1. one or more columns change in any row
2. a new row is added
3. an old is deleted
4. an ALTER TABLE of any kind

the UPDATE_TIME column in INFORMATION_SCHEMA.TABLES is updated.

You could find all changes in the last hour (or your selected interval) quite simply though:

SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS Table, UPDATE_TIME AS Updated
FROM INFORMATION_SCHEMA.TABLES
  WHERE DATE_SUB(NOW(), INTERVAL 1 HOUR) < UPDATE_TIME
  AND TABLE_SCHEMA != 'INFORMATION_SCHEMA'
  AND TABLE_TYPE = 'BASE TABLE';

I hope this helps.

SQL Server table creation date query

For 2005 up, you can use

SELECT
        [name]
       ,create_date
       ,modify_date
FROM
        sys.tables

I think for 2000, you need to have enabled auditing.

How do I get the creation date of a MySQL table?

You would query the information_schema for the create_time of the table.

For instance:

SELECT create_time FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'your_schema'
  AND table_name = 'your_table'

Reference: http://dev.mysql.com/doc/refman/5.0/en/tables-table.html

---

Related Topics