Cleaning up "rotten" tables - need help with code
-
I'm not looking for exact code, more like suggestions of what types of things I can do...
We have an INSERT process that pulls data from a vended system into a local table for consumption. This process was set up so we could run this multiple times a day if needed, and archives off "today's" data into an archive database before deleting and re-inserting the data into our local database. Unfortunately, I just realized that it creates the backup table on the archive database whether or not there are records to be archived. Meaning, on the first run of the day, it will create an empty table with the date over in the archive DB but since there are no records being deleted, the table itself is empty.
Table names are named like below and the first iteration may or may not have data in it, so I can't just use the lack of an iteration number to choose to delete.
MyTable_20160116 (has data)
MyTable_20160116_1 (has data)
MyTable_20160116_2 (has data)
MyTable_20160117 (no data)
MyTable_20160118 (no data)
MyTable_20160119 (has data)
MyTable_20160120 (has data)
MyTable_20160120_1 (has data)
MyTable_20160121 (no data)
MyTable_20160122 (has data)
We have about 150+ tables (probably more) in the archive database that I need to go through and remove, which means finding out which ones have data and which ones don't. What I'd like to do is loop through them to see if there's a record, then drop the table if there's no records. The ms_foreachtable is too cumbersome for this task because there are hundreds of other tables in the archive db that I know I can ignore.
I'm in the middle of writing a cursor for this, but I am getting annoyed at the cursor because I have to also write dynamic SQL to get the dated table names.
Is the cursor the best way to go on this? Or is there a system catalog I can check for whether or not tables have data in them?
-
I'd write a query to identify (only) the empty tables. Add a column to the query which returns the 'DROP TABLE ...' statement, for the selected table.
Execute the query, copy the column containing the generated SQL, paste into SSMS, and hit F5.
Do it in a test DB first 🙂
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 -
Phil Parkin (9/22/2016)
I'd write a query to identify (only) the empty tables. Add a column to the query which returns the 'DROP TABLE ...' statement, for the selected table.Execute the query, copy the column containing the generated SQL, paste into SSMS, and hit F5.
Do it in a test DB first 🙂
That's what I'm already doing.
What I'm looking for is a way to make the query easier. As in, is there a system catalog that might already have this information? Or some way other then selecting on the individual tables to see if data is in there?
-
This should do what you need; first confirming that the table name matches pattern, next confirming that table is empty, and then dropping the table.
For some damn reason, SQLServerCentral throws a "This page can’t be displayed" error whenever I attempt to paste the t-sql code, but below is a link to a screenshot.
http://s17.p o s t i m g.org/h0e8w2ndb/Capture.png
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
Something like this?
SELECT tbl.tbl, sql = CONCAT('DROP TABLE ',tbl.sch,'.',tbl.tbl)
FROM sys.dm_db_partition_stats ddps
CROSS APPLY (SELECT sch = OBJECT_SCHEMA_NAME(object_id)
, tbl = OBJECT_NAME(ddps.object_id)
) tbl
WHERE (
ddps.index_id = 0
OR ddps.index_id = 1
)
AND tbl.tbl LIKE 'MyTable_2016%'
GROUP BY CONCAT('DROP TABLE ',tbl.sch,'.',tbl.tbl)
, tbl.tbl
HAVING SUM(ddps.row_count) = 0;
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 -
You could capture the SQL statements in a variable and then execute it:
DECLARE @sql nvarchar(max);
SET @sql = '';
SELECT @sql +='DROP TABLE [' + s.name + '].[' +tab.name + '];' + CHAR(10)
FROM sys.dm_db_partition_stats AS part
INNER JOIN sys.objects tab
ON part.object_id = tab.object_id
AND part.index_id IN (0, 1)
INNER JOIN sys.schemas s
ON tab.schema_id = s.schema_id
WHERE objectproperty(part.object_id,'IsMSShipped') = 0
GROUP BY part.object_id, s.name, tab.name
HAVING SUM(part.row_count) = 0
PRINT @sql
-
The row_count in sys.partitions would have the record counts. As already indicated, you could just generate the drop statements using a query with sys.partitions -
SELECT 'DROP TABLE ' + s.name +'.'+ t.name + CHAR(13)FROM sys.tables t
INNER JOIN sys.partitions p
ON p.object_id = t.object_id
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0
AND p.index_id IN (1,0)
GROUP BY s.name,t.name
HAVING SUM(p.rows) = 0
Sue
-
I would:
Change the process so it doesn't generate empty tables.
Then filter the table list where name contains "MyTable_".
Open Object Explorer Details, right click column headers and include "Row Count" in the display
Sort list by row count.
Highlight all empty tables.
Delete.
Never look back.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);- 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] -
Thanks, guys. But you're all missing the point of my question.
I withdraw it since no one understands what I'm asking for.
-
mister.magoo (9/22/2016)
I would:Change the process so it doesn't generate empty tables.
Yeah, already doing that.
Open Object Explorer Details, right click column headers and include "Row Count" in the display
Sort list by row count.
Highlight all empty tables.
YES! This is what I needed. Thank you.
-
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.
Yep, I am totally puzzled as to why MM's idea worked for you and mine did not 🙂
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 -
Here's the code you're not looking for 😉
/* drop empty "MyTable%" tables */
DECLARE @exec_sql bit
DECLARE @print_sql bit
DECLARE @sql nvarchar(4000)
DECLARE @table_has_row bit
DECLARE @table_name nvarchar(128)
SET @exec_sql = 0
SET @print_sql = 1
DECLARE cursor_tables CURSOR LOCAL FAST_FORWARD FOR
SELECT t.name
FROM sys.tables t
WHERE t.name LIKE 'mytable%'
/* AND t.create_date > DATEADD(DAY, -1, GETDATE()) */ /*and table is at least 24 hours old*/
ORDER BY t.name
OPEN cursor_tables
IF @print_sql = 1
SET NOCOUNT OFF
ELSE
SET NOCOUNT ON;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM cursor_tables INTO @table_name;
IF @@FETCH_STATUS <> 0
IF @@FETCH_STATUS = -1
BREAK
ELSE
CONTINUE;
SET @sql = 'SELECT @table_has_row = CASE WHEN EXISTS(SELECT TOP (1) 1 FROM dbo.[' + @table_name + ']) THEN 1 ELSE 0 END'
IF @print_sql = 1
PRINT @sql
/*this sql must run every time, as it provides control info to the code*/
EXEC sys.sp_executesql @sql, N'@table_has_row bit OUTPUT', @table_has_row OUTPUT
IF @table_has_row = 0
BEGIN
SET @sql = 'DROP TABLE dbo.[' + @table_name + '];'
IF @print_sql = 1
PRINT @sql
IF @exec_sql = 1
EXEC(@sql)
END /*IF*/
END /*WHILE*/
DEALLOCATE cursor_tables
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".
-
Phil Parkin (9/22/2016)
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.
Yep, I am totally puzzled as to why MM's idea worked for you and mine did not 🙂
I was hoping to find a single table and didn't realize there was a GUI solution to this problem. His solution allowed me to stop writing code.
-
Brandie Tarvin (9/22/2016)
Phil Parkin (9/22/2016)
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.
Yep, I am totally puzzled as to why MM's idea worked for you and mine did not 🙂
I was hoping to find a single table and didn't realize there was a GUI solution to this problem. His solution allowed me to stop writing code.
OK, but so did mine ... because it wrote the code for you. No problem, it was fun to write anyway 🙂
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 -
I view it differently, since I prefer a code-based solution to a gui-based solution.
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".
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply