SQL Server: Find Table Creation Date

One of my production database has thousands of tables. I was trying to find the created date time and modified date time of  few of the tables. I know the that we can find the created date and time through the table’s properties window in object explorer in management studio. But I want to know some better and easy way to find it. After some searching I’ve figured out the below methods to find the table creation date and time. I’ve tried the below options on SQL Server 2014.

Table Created Date And Time Using SQL Query

The easiest and the fastest way to find the created and modified date of a table or a group of tables created during a specific period is by using a simple query against the system table sys.tables.

1. The simple query to list down the created date and modified date of all the tables in a database is:

SELECT
     name, object_id, create_date, modify_date
FROM
     sys.tables

2. Query to fetch the created date and time of a table or group of tables:

SELECT
     name, object_id, create_date, modify_date, *
FROM
     sys.tables 
WHERE 
	 name like 't%'

3. Query to fetch the list of tables created or modified during a certain period:

SELECT
     name, object_id, create_date, modify_date, *
FROM
     sys.tables 
WHERE 
	 create_date between '2015-03-01' and '2015-03-30' and
	 modify_date between '2015-03-01' and '2015-03-30'

Table Creation Date And Time From Object Explorer in SSMS

1. Using Filter Settings

Another easy way to use the Filter Settings of the Tables folder in object explorer.

1. In the Object Explorer in SQL Server Management Studio, go to the database and expand it.
2. Right Click the Tables folder and select Filter in the right-click menu.
3. Under filter select Filter Settings. The filter settings window will pop up.
   
4. In the filter settings window against the property Creation Date, you can choose one of the Operator from the list (Equals, Less than, Greater than, Between, etc..) and enter the required date under Value and then click OK.
   
5. The list of tables under the Tables folder are filtered based on your creation date criteria.

2. Using Property Settings

Finally, you can also find the created date and time of a table is to open the properties window of the table from object explorer. This is an easy way if the database has few tables. This way of identifying the table’s created date will become difficult if the database has thousands of tables. In that case you may need to use the filter option in the tables folder instead of scrolling all the tables. To see the table created date and time from properties window:

1. In the Object Explorer in SQL Server Management Studio, go to the database and expand it.
2. Under the Tables folder select the table name.
3. Right click and select Properties from the menu.
4. You will see the created date of the table in the General section under Description.

Related

• Read the article Find Size Of Tables In Database to find the size of the table.

Reference

• Stackoverflow.com