0

I want to find Schema creation date and user who created schema in SQL server, I am unable to find information in system views.

Note: Not asking about schema scoped objects

Navyasri Balina
  • 3
  • 1
  • 3

2 Answers2

0

You may find the information in sys.traces as long as the information not reset which happens upon service restart.

If that's not the case, try following query, but this is quick solution, the proper solution in your case is Extended Events which stores the selected trace events information outsize SQL Databases, so that you can retrieve and monitor such event info whenever you need since the captured data won't be retested upon service restart.

For more details on all event classes and sys.traces

select PATH, Trace.*
from sys.traces AS T
        CROSS APPLY fn_trace_gettable(T.path, default) as Trace
Where ObjectName like '%YourSchema%'
go
Shekar Kola
  • 2,477
  • 2
  • 10
  • 24
0

There isn't really a reliable way... but given that, having created a schema, you're going to create your first object in it pretty soon after that:

select [schema_id], min(create_date) create_date
 from sys.all_objects with (nolock)
 group by [schema_id]
Dan Sutton
  • 1