who created a sql table

Is there a way to find out who created a sql table? We have tables that were created over the past 5 years all owned by dbo. Can I find any more information on the creation?
usky1Asked:
Who is Participating?
 
DevDenCommented:
Do you really need to know who created them or are you trying to find out if they are in use by any production systems?

You can use queries like:

SELECT *
FROM syscomments
WHERE text LIKE '%your_table_name%'

to find out where they are used.

see this article for more info:

http://thomaslarock.com/2011/03/march-madness-sql-server-system-tables-syscomments/

Finding out which apps use the tables by digging and asking around might also give you an idea who created them.
0
 
chandrasekar1Commented:
SELECT o.object_id, o.name,

CASE WHEN principal_id is NOT NULL THEN (SELECT name FROM sys.database_principals dp WHERE dp.principal_id=o.principal_id)

ELSE (SELECT dp.name FROM sys.database_principals dp,sys.schemas s WHERE s.schema_id=o.schema_id and s.principal_id=dp.principal_id)

END as Owner

FROM sys.objects o

WHERE type='U'

Open in new window


src link
http://www.sqlservercentral.com/Forums/Topic324773-149-1.aspx#bm324899
0
 
_agx_Commented:
.. won't that just return 'dbo' ?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
usky1Author Commented:
agx - that is all it does return. Do you have any suggestions?
0
 
_agx_Commented:
Sorry, no. I'm not sure the system tables store even store additional info. Beyond the owner, creation/modification that is. Especially if they were created by a sql login like "sa" or something.  
0
 
OP_ZaharinCommented:
- no, you probably can't. there is a method to get such information from the transaction log but not sure if its still hold the 5 years back log:

http://www.sqlskills.com/BLOGS/PAUL/post/Finding-out-who-dropped-a-table-using-the-transaction-log.aspx
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Just confirming that there is no way. "dbo" is always replacing real user's info, even if Windows Authentication is used. Only if there is no mapping to dba, the user is maintained as schema owner - and again, nowhere else.
0
 
usky1Author Commented:
Sorry about the delay in responding.

The article is what i was looking for.   Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.