Delete Old Tables On a Schedule Part Two: The Stored Proc

Been a massively busy week, what with deploying clustered SQL servers on Sunday, Release this weekend, new project starting next week for the dev team, so that means it starts this week for me, plus the usual drama of month end loading.

Today, I have been up since 5am working with cubes using PowerShell. So am still telecommuting. My own proclivities to the activity are documented here and to further the conversation the article linked below is an article on the Business Insider website about how metrics were used to make the decision. Apparently someone checked the VPN to see if people were logging in enough, and apparently they weren’t. I’m good friends with one of the network guys, so I asked him about the VPN stats we have. Turns out you can se how much someone has downloaded in a session, allowing people to see just how much traffic you’re making. The network guy pulled up the stats and showed me one person that logged on for an hour, but the traffic was barely enough for him to check his emails. Conversely, some sessions were in the GB’s w/r/t network traffic. I asked him how if the management ever ask to see these stats. “Often” was the reply.

http://www.businessinsider.com/how-marissa-mayer-figured-out-work-at-home-yahoos-were-slacking-off-2013-3

I also discovered that my script I wrote in this post was not running as a job, so made a stored proc and I call that another way. I have made this more agnostic so that you can select the schema you want to drop the tables from.


USE DbName
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[DropTables]
(

@LengthOfTime int,
@schema VARCHAR(20)
)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @tname VARCHAR(100)
DECLARE @sql VARCHAR(max)

DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name AS tname
FROM sys.objects
WHERE create_date > DATEADD (hh, -@LengthOfTime,  CAST(GETDATE() AS datetime))
and type = 'U'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'DROP TABLE ' +@schema + '.' + @tname
EXEC (@sql)
--PRINT @sql
FETCH NEXT FROM db_cursor INTO @tname
END
CLOSE db_cursor
DEALLOCATE db_cursor
END

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s