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 ) Goa) 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.
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
- Dozens more answers to tough SQL Server questions from Michael Hotek are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.