Cleaning up "rotten" tables - need help with code

Question

Post reply

Cleaning up "rotten" tables - need help with code

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Phil Parkin SSC Guru Points: 246961 More actions · Answer 1

ScottPletcher (9/22/2016)
I view it differently, since I prefer a code-based solution to a gui-based solution.

+1

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

😀

I prefer to do what needs to be done then get on with writing positive code - cleanup code is for DBAs 😀

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Sue_H SSC Guru Points: 90860 More actions · Answer 3

ScottPletcher (9/22/2016)
I view it differently, since I prefer a code-based solution to a gui-based solution.

Yup. Lots of shops where it's the only allowable solution as well, requiring scripts to be checked into source control for all changes going into Prod.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 4

Brandie Tarvin (9/22/2016)
Thanks, guys. But you're all missing the point of my question.
I withdraw it since no one understands what I'm asking for.

Would this work? First it checks if table name matches pattern, next it checks if table contains at least one row, and if not then it drops the table.

exec sp_MsForEachTable

'

if ''?'' like ''MyTable%''

begin;

if not exists ( select 1 from ?)

begin;

print ''Dropping empty table: ?'';

d r o p table ?;

end;

';

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

I'd be in deep Kimchie if I deleted some of my empty tables because they're used to prop up some very necessary "Instead of" triggers on a View that we created to get around some colossal fubar built by my predecessors that needed to be fixed without changing a mountain of code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sue_H SSC Guru Points: 90860 More actions · Answer 6

Jeff Moden (9/22/2016)
I'd be in deep Kimchie if I deleted some of my empty tables because they're used to prop up some very necessary "Instead of" triggers on a View that we created to get around some colossal fubar built by my predecessors that needed to be fixed without changing a mountain of code.

Good point. Should have rollback scripts for changes anyway and they would recreate the tables if needed.

Sue

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 7

Here's an easy option to clean up the existing empty tables...

DECLARE @DropTables VARCHAR(8000) = '';

SELECT

@DropTables = CONCAT(@DropTables, CHAR(13), 'DROP TABLE ', s.name, '.', o.name, ';')

FROM

sys.objects o

JOIN sys.schemas s

ON o.schema_id = s.schema_id

CROSS APPLY (-- prevents duplication if more that 1 partition exists for a given table.

SELECT

TotalRows = SUM(p.rows)

FROM

sys.partitions p

WHERE

o.object_id = p.object_id

) px

WHERE 1 = 1

--AND o.name LIKE 'MyTable%'

AND s.name = 'dbo'

AND px.TotalRows = 0

PRINT(@DropTables);

--EXEC(@DropTables);

ScottPletcher SSC Guru Points: 101098 More actions · Answer 8

Personally I wouldn't rely on sys.partitions to gauge whether a table is completely empty or not. Maybe just me. Just out of curiosity, what if a table load is in-flight at the time sys.partitions is checked?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 9

Good call Scott... I wasn't even thinking of the "“approximate number of rows for this partition” gotcha...

Thanks for the reminder. 🙂

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 10

Jeff Moden (9/22/2016)
I'd be in deep Kimchie if I deleted some of my empty tables because they're used to prop up some very necessary "Instead of" triggers on a View that we created to get around some colossal fubar built by my predecessors that needed to be fixed without changing a mountain of code.

In this particular case, I wrote the code that created the tables and they were just being used in case we needed to rerun a process during the day. It's just that I didn't think things through a few years ago when I initially did the backup part of this proc and forgot to use a check to verify there were records to backup before creating the table.

So the only thing depending on the tables was this one proc. Therefore, there was no trouble to be gotten into for cleaning up after myself.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.