who created a sql table

Posted on 2011-05-12
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?
Question by:usky1
    LVL 3

    Expert Comment

    SELECT o.object_id,,
    CASE WHEN principal_id is NOT NULL THEN (SELECT name FROM sys.database_principals dp WHERE dp.principal_id=o.principal_id)
    ELSE (SELECT 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
    LVL 51

    Expert Comment

    .. won't that just return 'dbo' ?

    Author Comment

    agx - that is all it does return. Do you have any suggestions?
    LVL 51

    Expert Comment

    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.  
    LVL 23

    Expert Comment

    - 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:
    LVL 67

    Expert Comment

    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.
    LVL 1

    Accepted Solution

    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:

    Finding out which apps use the tables by digging and asking around might also give you an idea who created them.

    Author Closing Comment

    Sorry about the delay in responding.

    The article is what i was looking for.   Thanks

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    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.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now