September 22, 2016 at 3:07 pm
I apologize for any short comings in advance as I am not all that familiar with SQL. I need to create an SQL Server Agent Job that truncates tables out of a database. The prefix that is shared is 'dbo.ntEventLog' ... everyday it suffixes a date stamp in YYYYMMDD format or 'dbo.ntEventLogYYYYMMDD'. SO I was hoping to utilize a script that will capture and tables with that prefix and truncate them.
September 22, 2016 at 9:53 pm
You could use something like this.. I would recommend commenting out the EXEC statement and just printing the SQL to make sure the SQL statement is correct.
USE [mydatabase];
GO
-- Declare the variables to store the values returned by FETCH.
DECLARE @tableName VARCHAR(50);
DECLARE @sql VARCHAR(100);
DECLARE table_cursor CURSOR FAST_FORWARD FOR
SELECT name
FROM sys.all_objects
WHERE sys.all_objects.name LIKE 'tbl%'
AND sys.all_objects.type = 'U';
OPEN table_cursor;
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
FETCH NEXT FROM table_cursor
INTO @tableName;
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- PRINT @tableName;
SET @sql = 'TRUNCATE TABLE ' + @tableName + ';' -- create the SQL statement
EXEC (@sql); -- execute the SQL statement
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM table_cursor
INTO @tableName;
END
CLOSE table_cursor;
DEALLOCATE table_cursor;
GO
September 23, 2016 at 11:54 am
Are these tables going to be repopulated after they're truncated? If not, why would you keep these tables at all (truncate rather than drop)?
September 23, 2016 at 12:38 pm
pietlinden (9/22/2016)
You could use something like this.. I would recommend commenting out the EXEC statement and just printing the SQL to make sure the SQL statement is correct.
Too many rows. This is simpler.
DECLARE @sql NVARCHAR(MAX) = '';
-- create the SQL statement
SELECT @sql += 'TRUNCATE TABLE ' + QUOTENAME(name) + ';' + CHAR(10)
FROM sys.tables
WHERE name LIKE 'PerfTest%';
-- execute the SQL statement
EXEC sp_executesql @sql;
September 26, 2016 at 9:23 am
They get repopulated by the host application as it seems, and none of these work for me, I am wondering if it is considering the "." in the table name as a special character.
September 26, 2016 at 9:39 am
Do you realize that dbo is not part of the table name? It's the schema name.
In the case that you really have a point in your table name, the option I included would handle it as well as other special characters.
September 26, 2016 at 9:48 am
mr_xtant (9/26/2016)
They get repopulated by the host application as it seems, and none of these work for me, I am wondering if it is considering the "." in the table name as a special character.
Did you change the filter in the queries to point to your tables? Along the lines of:
DECLARE @sql as nvarchar(max) = ''
SELECT @sql = @sql + 'TRUNCATE TABLE dbo.[' + name + '];' + CHAR(10)
FROM sys.tables
WHERE name like 'ntEventLog%'
print @sql
--exec sp_executesql @sql
Sue
September 26, 2016 at 10:02 am
Sue_H (9/26/2016)
mr_xtant (9/26/2016)
They get repopulated by the host application as it seems, and none of these work for me, I am wondering if it is considering the "." in the table name as a special character.Did you change the filter in the queries to point to your tables? Along the lines of:
DECLARE @sql as nvarchar(max) = ''SELECT @sql = @sql + 'TRUNCATE TABLE dbo.[' + name + '];' + CHAR(10)
FROM sys.tables
WHERE name like 'ntEventLog%'
print @sql
--exec sp_executesql @sql
Sue
Yes, I did, tables are still at size (10-11GB)
September 26, 2016 at 10:34 am
Can you share the code that you're using? (you can change the filter if needed)
Also, how are you validating the size? Why are you using disk space to measure them instead of row counts?
Here's a query with validation that would prevent the tables to be repopulated before the check.
BEGIN TRANSACTION;
DECLARE @sql NVARCHAR(MAX) = '';
-- create the SQL statement
SELECT @sql += 'TRUNCATE TABLE ' + QUOTENAME(name) + ';' + CHAR(10)
FROM sys.tables
WHERE name LIKE 'ntEventLog%';
-- execute the SQL statement
EXEC sp_executesql @sql;
--Validate that it worked
SELECT t.name, SUM(p.row_count) AS Row_count
FROM sys.tables t
JOIN sys.dm_db_partition_stats p ON t.object_id = p.object_id
WHERE p.index_id IN (0,1)
AND t.name like 'ntEventLog%'
GROUP BY t.name;
COMMIT TRANSACTION;
September 28, 2016 at 11:36 am
For about the 3rd time in the past week... sp_MsForEachTable to the rescue.
exec sp_MsForEachTable
'
if ''?'' like ''ntEventLog%''
begin;
print ''truncating.. ?'';
truncate table ?;
end;
';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 12, 2016 at 2:39 pm
I am verifying the space used by using Management Studio, right clicking on the table object in question, choosing properties, and looking at storage. So far, nothing has worked, looks like I am doomed at maintaining this on a daily basis manually.
October 12, 2016 at 2:44 pm
mr_xtant (10/12/2016)
I am verifying the space used by using Management Studio, right clicking on the table object in question, choosing properties, and looking at storage. So far, nothing has worked, looks like I am doomed at maintaining this on a daily basis manually.
Did you try my code that included the validation?
October 13, 2016 at 8:45 am
"I am not all that familiar with SQL. I need to create an SQL Server Agent Job that truncates tables out of a database. "
I'd have thought it safer to get somebody in your organisation who is familiar with SQL to remove data.
October 13, 2016 at 11:36 am
Decades ago, in 1960's, we used magnetic tape. The IBM convention was a prefix, followed by a two digit year and a three digit ordinal date within the year. Tape library systems tracked which tapes needed to be restored, deleted, and rewritten. This was part of the Y2K problem; the type the tape library software got screwed up in the year 2000.
Congratulations! you just rediscovered and re-implemented technology that you to become obsolete about 50 years ago. Why are you truncating these tables instead of dropping them if there no longer needed? But a better question is why is a date, which is an attribute of an entity, being used as metadata?
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
October 13, 2016 at 7:04 pm
CELKO (10/13/2016)
But a better question is why is a date, which is an attribute of an entity, being used as metadata?
It's probably not applicable for this thread but the answer to your question can be covered in one word... partitioning.
Also, it's not likely that the OP designed the system you're complaining about.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply