Determining user who created an object

I would like to write a procedure that populates a table:

 Create Table Test ( UserName Varchar(50), DateCreated datetime, Object_Type Varchar(20), ObjectName Varchar(50), No_Of_Rows int, Space_Wasted int, Action_Flag char ) Go
a) The field Username is the name of the user who created the object. Where do I get that value?
b)The DateCreated is okay. I will use the getdate() function and set it as a default.
c) Object type is ie. Table, View, SP, etc.
d) Object name is the name of the table, name of view, etc.
e) No_Of_Rows is the number of rows of that object and should be updated each time the table has more records added to it.
f) Action_Flag is either 1,2,3 or 4. These will signify the age of the object. If it's more than three months, it will be archived then deleted from the database.

The reasons why I'm writing this procedure:
a) To keep track of all objects created and by whom
b) How much space is that object taking?
c) For archiving purposes. Anything older than three months will be identified and an alert to be sent to the user.

My problem is that we are using NT authentication, but the objects being created are dbo.vvvvv, which makes it difficult to capture the user name. I need pointers on how to best approach this problem. Thanks in advance.

A. suser_sname()

Have you looked into sp_spaceused??? This will report virtually all of the information you are asking for. You can also get the date the object was created from the system tables.


For More Information

Dig Deeper on Microsoft SQL Server Installation

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.