?
Solved

who created a sql table

Posted on 2011-05-12
9
Medium Priority
?
171 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:usky1
8 Comments
 
LVL 3

Expert Comment

by:chandrasekar1
ID: 35748351
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
 
LVL 52

Expert Comment

by:_agx_
ID: 35748500
.. won't that just return 'dbo' ?
0
 

Author Comment

by:usky1
ID: 35749688
agx - that is all it does return. Do you have any suggestions?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 52

Expert Comment

by:_agx_
ID: 35751555
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35751750
- 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
 
LVL 71

Expert Comment

by:Qlemo
ID: 35867657
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
 
LVL 1

Accepted Solution

by:
DevDen earned 1000 total points
ID: 35869480
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
 

Author Closing Comment

by:usky1
ID: 35893114
Sorry about the delay in responding.

The article is what i was looking for.   Thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question