When a Table Was Created: Find It With Metadata and Traces

To find out when a table was created, read create_date from sys.tables. The default trace and an Extended Events session answer the next question, which is who created it.

The Date Is Already in the Catalog

Every table has a row in sys.tables, and the row holds two dates. The column create_date is the moment of creation. The column modify_date changes when the table definition changes. Nothing needs to be turned on, and the answer is there for tables created years ago.

The demo uses a database named TableBirthDemo with one table. It creates the table, reads the dates, and later changes the table to see which date moves.

IF DB_ID(N'TableBirthDemo') IS NULL CREATE DATABASE TableBirthDemo;
GO
USE TableBirthDemo;
GO
DROP TABLE IF EXISTS dbo.Orders, dbo.Sales;
CREATE TABLE dbo.Orders (OrderID int PRIMARY KEY, Item nvarchar(40) NOT NULL);
GO
SELECT name, create_date, modify_date FROM sys.tables WHERE name = N'Orders';

Both dates are the same right after the create. Your values are the time you ran the script. Now wait two seconds, rename the table, add a column, and compare. SQL Server prints a caution after sp_rename. It is only a warning.

DECLARE @created datetime = (SELECT create_date FROM sys.tables WHERE name = N'Orders');
WAITFOR DELAY '00:00:02';
EXEC sp_rename N'dbo.Orders', N'Sales';
ALTER TABLE dbo.Sales ADD Note nvarchar(20) NULL;
SELECT name,
       CASE WHEN create_date = @created THEN N'unchanged' ELSE N'changed' END AS CreateDate,
       CASE WHEN modify_date > create_date THEN N'later' ELSE N'same' END AS ModifyDate
FROM sys.tables WHERE name = N'Sales';

The rename and the new column moved modify_date and left create_date alone. The creation date follows the table through renames and changes. A table that is dropped and created again is a new table with a new date. A table rebuilt by a deployment script gets a new date too. A recent date can mean a redeploy, not a new table.

Who Created It: the Default Trace

The catalog does not record the login, and the default trace does. It runs on every SQL Server unless someone turned it off. That is why it can answer who created a table. The setting is named default trace enabled, and the test server has it at 1. The same trace is the source for Who Changed a Server Setting in SQL Server? How to Find Out.

The trace keeps its events in a few files that roll over. The script reads the base file name, and SQL Server walks all the files. Event 46 is Object:Created. Each creation appears twice, once at the start and once at the commit, and the commit has EventSubClass 1. The query keeps the commit rows for user tables. It matches each row to the table by object id and by time. The path code uses Windows folder names, so use / on Linux.

DECLARE @path nvarchar(260) = (SELECT path FROM sys.traces WHERE is_default = 1);
SET @path = LEFT(@path, LEN(@path) - CHARINDEX(N'\', REVERSE(@path))) + N'\log.trc';
SELECT te.name AS EventName, t.ObjectName AS NameWhenCreated, o.name AS NameNow,
       CASE WHEN t.LoginName IS NOT NULL THEN N'Yes' ELSE N'No' END AS LoginRecorded
FROM sys.fn_trace_gettable(@path, DEFAULT) AS t
JOIN sys.trace_events AS te ON te.trace_event_id = t.EventClass
JOIN sys.objects AS o ON o.object_id = t.ObjectID
WHERE t.EventClass = 46 AND t.EventSubClass = 1 AND t.ObjectType = 8277
  AND t.DatabaseName = DB_NAME() AND o.name = N'Sales'
  AND ABS(DATEDIFF(SECOND, t.StartTime, o.create_date)) <= 2;

The row shows the name the table had at creation, which differs from its name today. Add t.LoginName, t.HostName and t.ApplicationName to the select list to see who created it and from where. The demo leaves those out of the table. The time filter exists for a reason. A dropped and recreated table can reuse an object id. Old events with the same id stay in the file. Reading a trace file with fn_trace_gettable needs the ALTER TRACE permission.

The default trace keeps a limited history. It holds five files of 20 MB each. On a busy server, old events roll out within days. When the event is gone, create_date still tells you when, and nothing tells you who.

Look Forward With Extended Events

SQL Trace is deprecated, and it still works on SQL Server 2025. Microsoft recommends Extended Events for new work. An Extended Events session records table creation from the moment you start it. The session below keeps the events in memory, filters on one database, and reads the application name. It needs the ALTER ANY EVENT SESSION permission, or CONTROL SERVER before SQL Server 2022.

IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'TableBirthWatch') DROP EVENT SESSION TableBirthWatch ON SERVER;
GO
CREATE EVENT SESSION TableBirthWatch ON SERVER
ADD EVENT sqlserver.object_created (
    ACTION (sqlserver.client_app_name)
    WHERE sqlserver.database_name = N'TableBirthDemo')
ADD TARGET package0.ring_buffer (SET max_memory = 512)
WITH (STARTUP_STATE = OFF);
GO
ALTER EVENT SESSION TableBirthWatch ON SERVER STATE = START;

Create a table, then read the ring buffer. The event has a begin and a commit phase. It also records indexes and statistics. The query keeps the committed user tables.

DROP TABLE IF EXISTS dbo.Shipments;
CREATE TABLE dbo.Shipments (ShipmentID int PRIMARY KEY);
GO
SELECT e.x.value('(data[@name="object_name"]/value)[1]', 'nvarchar(128)') AS ObjectName,
       e.x.value('(data[@name="object_type"]/text)[1]', 'nvarchar(60)') AS ObjectType,
       e.x.value('(data[@name="ddl_phase"]/text)[1]', 'nvarchar(30)') AS Phase
FROM sys.dm_xe_session_targets AS st
JOIN sys.dm_xe_sessions AS s ON s.address = st.event_session_address
CROSS APPLY (SELECT CAST(st.target_data AS xml)) AS d(x0)
CROSS APPLY d.x0.nodes('RingBufferTarget/event') AS e(x)
WHERE s.name = N'TableBirthWatch' AND st.target_name = N'ring_buffer'
  AND e.x.value('(data[@name="object_type"]/text)[1]', 'nvarchar(60)') = N'USRTAB'
  AND e.x.value('(data[@name="ddl_phase"]/text)[1]', 'nvarchar(30)') = N'Commit';

The session caught the creation with the table name. Add the action sqlserver.server_principal_name to the session to record the login as well. Write the events to a file target instead of memory when you want to keep them for weeks.

Do You Need More Than create_date?

You could argue that create_date is enough. It tells you when a table was created, and that settles most questions. Who created a table matters during an audit or a cleanup of unknown tables. Start with the date. Reach for the trace when someone asks who, and start the Extended Events session before the next question arrives.

What to Remember

To find when a table was created, read create_date, because renames and changes do not move it. To find who created it, query the default trace for event 46 and match it by time. Remember that the trace rolls over. Use Extended Events when the history must last.

When you finish, remove the session and drop the demo database.

USE master;
GO
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'TableBirthWatch')
BEGIN
    ALTER EVENT SESSION TableBirthWatch ON SERVER STATE = STOP;
    DROP EVENT SESSION TableBirthWatch ON SERVER;
END;
GO
ALTER DATABASE TableBirthDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE TableBirthDemo;

A creation date is not a signature, it is a timestamp with no name on it.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.