September 28, 2017 at 5:27 am
Hello All,
I have a large table "LARGETable" and till now my developers created a job that run every 2 minutes and delete records if the date is older than 2 months.
Of course it cause many locks.
Thus, I renamed the table name to "LARGETable_OLD" and also created new table with the same name "LARGETable" (with the same structure indexes, triggers etc) in one transaction.
I would like to create a job that will run every 2 months and will rename the table name "LARGETable" to "LARGETable_OLD" and will dynamically create the "LARGETable" with full structure (indexes, triggers etc).
Can you please advise?
Thanks in advance
September 28, 2017 at 5:35 am
89netanel - Thursday, September 28, 2017 5:27 AMHello All,
I have a large table "LARGETable" and till now my developers created a job that run every 2 minutes and delete records if the date is older than 2 months.
Of course it cause many locks.Thus, I renamed the table name to "LARGETable_OLD" and also created new table with the same name "LARGETable" (with the same structure indexes, triggers etc) in one transaction.
I would like to create a job that will run every 2 months and will rename the table name "LARGETable" to "LARGETable_OLD" and will dynamically create the "LARGETable" with full structure (indexes, triggers etc).
Can you please advise?
Thanks in advance
So, every two months, you want to start with an empty 'LARGETable'?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 28, 2017 at 5:42 am
Yes.
but i want to keep the data on table that will be call "LARGETable_OLD" (truncate is not an option).
September 28, 2017 at 5:53 am
Have you considered table partitioning?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2017 at 6:00 am
I don'r have many experience with partitioning.
and also the instance Edition is Standard 🙁
September 28, 2017 at 6:36 am
89netanel - Thursday, September 28, 2017 5:42 AMYes.but i want to keep the data on table that will be call "LARGETable_OLD" (truncate is not an option).
What do you want to happen to old versions of LARGETable_OLD? Will they be dropped and replaced every two months?
Also, if this is an archive table, why would you want it to retain triggers?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 28, 2017 at 6:37 am
exactly
September 28, 2017 at 6:43 am
Sounds like you should investigate the use of SYNONYMs.
Create two identical tables LARGETable1 and LARGETable2 and use a synonym to switch between them every two months (after truncating the 'current' _OLD version of the synonym).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 28, 2017 at 12:05 pm
89netanel - Thursday, September 28, 2017 6:00 AMI don'r have many experience with partitioning.
and also the instance Edition is Standard 🙁
Partitioning is in all editions from SQL 2016 SP1 onwards, so that's not a problem, and this kind of thing is what partitioning is for, so worth doing some reading.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2017 at 2:52 pm
Seems pretty simple. Instead of dropping the regular table, just make a copy of it and then truncate, that way, all your triggers, indexes, constraints, etc are not lost. You may not be able to truncate if there are FKeys, but this is the basic idea. Schedule a job to run this T-SQL:
DROP TABLE [LARGETable_OLD];
SELECT * INTO [LARGETable_OLD] FROM [LARGETable];
TRUNCATE TABLE [LARGETable];
October 3, 2017 at 2:10 am
Hi all,
What is the best way to see all the objects (indexes,PK,constraint,triggers) that related to my table "LARGETable" by a sql query?
October 3, 2017 at 9:29 am
For that, you can just right click on your table in SSMS Object Explorer and choose Script Table As -> Create To...
You can also right-click and choose "View Dependencies"..
You can also query the metadata, for instance, this will list views, triggers, functions and procedures that reference the table:
select
[o].[xtype]
, [o].[name]
, [c].[text]
, [o].[crdate] AS [createdDate]
from [sys].[syscomments] as [c]
join [sys].[sysobjects] as [o] on [c].[id] = [o].[id]
where text like '%LARGETable%'
order by 4 desc, 1, 2;
October 15, 2017 at 2:35 am
Hi All,
I found a solution!!!
i were looked for a script that will Dynamically create table with full structure.
I have 2 tables "workout" (with the most recent data) and "workout_old" (with data from the last 2 months)
and i wanted that every 2 months i will have a job that will automatically drop the old table "workout_old" than rename the "workout" table to "workout_old" and than create a table "workout" without any data but with all the constraints, indexes,triggers, etc
(For the first time I manually created a table that called "workout" and also an archive table that called "workout_old" that will keep data from last 2 moths.)
i created a job (that run in one transaction) that do the following:
1. run a script that generate the table structure of "workout" with all constraints, indexes,triggers, etc.
2. after the script generated i drop the old table "workout_old"
3. than rename the table "workout" to "workout_old"
4. and than create from section 1 the table "workout" with all constraints, indexes,triggers, etc
i used this script and changed it a little bit.
https://stackoverflow.com/questions/706664/generate-sql-create-scripts-for-existing-tables-with-query
/*
1. begin tran and set parameters
2. rename [WorkOut_old] to [WorkOut_old_delete]
3. execute the @sql that generate table script on [WorkOut]
4. export snapshot of the check constraints on table [WorkOut]
5. export snapshot of the triggers on table [WorkOut]
6. drop triggers from [WorkOut]
7. rename [WorkOut] to [WorkOut_old]
8. drop [WorkOut_old_delete]
9. sp_executesql @sql in order to create WorkOut
10. After the original table dropped and recreated - execute the Check Constraints snapshot #CheckC and recreate the Check Constraints
11. After the original table dropped and recreated - execute the trigger snapshot #tr and recreate the triggers
12. commit
*/
--1
BEGIN TRAN
DECLARE @Schema_Name NVARCHAR(1000)
,@Table_Name NVARCHAR(1000)
,@SchemaAndTable NVARCHAR(2000)
,@Object_Name NVARCHAR(2000)
,@Object_ID INT
,@SQL NVARCHAR(MAX)
,@DropTrigger NVARCHAR (max)
,@Table_Name_old NVARCHAR (1000)
,@Table_Name_old_delete NVARCHAR (1000)
SET @Schema_name = 'dbo'
SET @Table_Name = 'WorkOut'
SET @SchemaAndTable = @Schema_name+'.'+@Table_Name
SET @Table_Name_old = @Table_Name+'_old'
SET @Table_Name_old_delete = @Table_Name_old+'_delete'
--2
/**************************
Rename _old to _delete
***************************/
EXEC SP_RENAME @Table_Name_old,@Table_Name_old_delete;
--3
/**************************
Select @sql - Generate script table with PK,FK,indexes,triggers
***************************/
SELECT
@Object_Name = '[' + s.name + '].[' + o.name + ']'
, @Object_ID = o.[object_id]
FROM sys.objects o WITH (NOLOCK)
JOIN sys.schemas s WITH (NOLOCK) ON o.[schema_id] = s.[schema_id]
WHERE s.name + '.' + o.name = @SchemaAndTable
AND o.[type] = 'U'
AND o.is_ms_shipped = 0
;WITH index_column AS
(
SELECT
ic.[object_id]
, ic.index_id
, ic.is_descending_key
, ic.is_included_column
, c.name
FROM sys.index_columns ic WITH (NOLOCK)
JOIN sys.columns c WITH (NOLOCK) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
WHERE ic.[object_id] = @Object_ID
),
fk_columns AS
(
SELECT
k.constraint_object_id
, cname = c.name
, rcname = rc.name
FROM sys.foreign_key_columns k WITH (NOLOCK)
JOIN sys.columns rc WITH (NOLOCK) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id
JOIN sys.columns c WITH (NOLOCK) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id
WHERE k.parent_object_id = @Object_ID
)
SELECT @SQL =
'CREATE TABLE ' + @Object_Name + CHAR(13) + '(' + CHAR(13) + STUFF((
SELECT CHAR(9) + ', [' + c.name + '] ' +
CASE WHEN c.is_computed = 1
THEN 'AS ' + cc.[definition]
ELSE UPPER(tp.name) +
CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'
WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'
WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')
THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
WHEN tp.name = 'decimal'
THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
ELSE ''
END +
CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name ELSE '' END +
CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +
CASE WHEN dc.[definition] IS NOT NULL THEN ' DEFAULT' + dc.[definition] ELSE '' END +
CASE WHEN ic.is_identity = 1 THEN ' IDENTITY(' + CAST(ISNULL(ic.seed_value, '0') AS CHAR(1)) + ',' + CAST(ISNULL(ic.increment_value, '1') AS CHAR(1)) + ')' ELSE '' END
END + CHAR(13)
FROM sys.columns c WITH (NOLOCK)
JOIN sys.types tp WITH (NOLOCK) ON c.user_type_id = tp.user_type_id
LEFT JOIN sys.computed_columns cc WITH (NOLOCK) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id
LEFT JOIN sys.default_constraints dc WITH (NOLOCK) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id
LEFT JOIN sys.identity_columns ic WITH (NOLOCK) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
WHERE c.[object_id] = @Object_ID
ORDER BY c.column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')
+ ISNULL((SELECT CHAR(9) + ', PRIMARY KEY (' +
(SELECT STUFF((
SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
FROM sys.index_columns ic WITH (NOLOCK)
JOIN sys.columns c WITH (NOLOCK) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
WHERE ic.is_included_column = 0
AND ic.[object_id] = k.parent_object_id
AND ic.index_id = k.unique_index_id
FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''))
+ ')' + CHAR(13)
FROM sys.key_constraints k WITH (NOLOCK)
WHERE k.parent_object_id = @Object_ID
AND k.[type] = 'PK'), '') + ')' + CHAR(13)
+ ISNULL((SELECT (
SELECT CHAR(13) +
'ALTER TABLE ' + @Object_Name + /*' WITH'
+ CASE WHEN fk.is_not_trusted = 1
THEN ' NOCHECK'
ELSE ' CHECK'
END*/ +
' ADD'/*CONSTRAINT [' + fk.name + ']*/+' FOREIGN KEY('
+ STUFF((
SELECT ', [' + k.cname + ']'
FROM fk_columns k
WHERE k.constraint_object_id = fk.[object_id]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
+ ')' +
' REFERENCES [' + SCHEMA_NAME(ro.[schema_id]) + '].[' + ro.name + '] ('
+ STUFF((
SELECT ', [' + k.rcname + ']'
FROM fk_columns k
WHERE k.constraint_object_id = fk.[object_id]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
+ ')'
+ CASE
WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE'
WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'
WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT'
ELSE ''
END
+ CASE
WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE'
WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL'
WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT'
ELSE ''
END
+ CHAR(13) /*+ 'ALTER TABLE ' + @Object_Name + ' CHECK CONSTRAINT [' + fk.name + ']' + CHAR(13)*/
FROM sys.foreign_keys fk WITH (NOLOCK)
JOIN sys.objects ro WITH (NOLOCK) ON ro.[object_id] = fk.referenced_object_id
WHERE fk.parent_object_id = @Object_ID
FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')
+ ISNULL(((SELECT
CHAR(13) + 'CREATE' + CASE WHEN i.is_unique = 1 THEN ' UNIQUE' ELSE '' END
+ ' NONCLUSTERED INDEX [' + i.name + '] ON ' + @Object_Name + ' (' +
STUFF((
SELECT ', [' + c.name + ']' + CASE WHEN c.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
FROM index_column c
WHERE c.is_included_column = 0
AND c.index_id = i.index_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'
+ ISNULL(CHAR(13) + 'INCLUDE (' +
STUFF((
SELECT ', [' + c.name + ']'
FROM index_column c
WHERE c.is_included_column = 1
AND c.index_id = i.index_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')', '') + CHAR(13)
FROM sys.indexes i WITH (NOLOCK)
WHERE i.[object_id] = @Object_ID
AND i.is_primary_key = 0
AND i.[type] = 2
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
), '')
--PRINT @SQL
--4
/******
export snapshot of the check constraints on table
*******/
CREATE TABLE #CheckC (id INT IDENTITY(1,1),definition NVARCHAR (MAX))
INSERT INTO #CheckC
SELECT 'ALTER TABLE ' + @SchemaAndTable + ' ADD CHECK ' + ck.definition
FROM sys.check_constraints ck
INNER JOIN sys.tables t ON ck.parent_object_id = t.object_id
WHERE t.object_id=@Object_ID
--5
/******
export snapshot of the triggers on table
*******/
CREATE TABLE #tr (id INT IDENTITY(1,1),definition NVARCHAR (MAX))
INSERT INTO #tr
SELECT SM.definition
FROM sys.triggers TR
INNER JOIN sys.sql_modules SM
ON TR.object_id = SM.object_id
WHERE OBJECT_NAME(tr.parent_id)= @Table_Name
--6
/**************************
Drop Triggers
***************************/
SELECT @DropTrigger =
ISNULL(((
SELECT CHAR(13)+'DROP TRIGGER '+ obj.name+CHAR(13) from sys.sql_modules m
INNER JOIN sys.objects obj on obj.object_id=m.object_id
INNER JOIN sys.tables tbl on tbl.object_id = obj.parent_object_id
WHERE obj.type ='TR'
AND tbl.name = @Table_Name
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
), '')+ CHAR(13)
-- print @DropTrigger
EXEC sys.sp_executesql @DropTrigger
--7
/**************************
Rename the table to _old
***************************/
EXEC sp_rename @Table_Name, @Table_Name_old;
--8
/**************************
Drop the table _old_delete
***************************/
DECLARE @DropTable_old_delete NVARCHAR (MAX)
SET @DropTable_old_delete = N'drop table '+QUOTENAME(@Schema_name)+'.'+QUOTENAME(@Table_Name_old_delete)+';'
EXEC sys.sp_executesql @DropTable_old_delete
--9
/**************************
sp_execute @sql
***************************/
EXEC sys.sp_executesql @SQL
--10
/********
After the original table dropped and recreated
execute the Check Constraints snapshot and recreate the Check Constraints
********/
DECLARE @CKCCounter INT,
@CKCMax INT,
@CKCCommand NVARCHAR (MAX)
SELECT @CKCMax = MAX(id) FROM #tr
SET @CKCCounter = 1
WHILE @CKCCounter <= @CKCMax
BEGIN
SELECT @CKCCommand = definition FROM #CheckC WHERE id = @CKCCounter
EXEC sp_executesql @CKCCommand
SET @CKCCounter = @CKCCounter+1
END
DROP TABLE #CheckC
--11
/********
After the original table dropped and recreated
execute the triggers snapshot and recreate the triggers
********/
DECLARE @TRCounter INT,
@TRMax INT,
@TRCommand NVARCHAR (MAX)
SELECT @TRMax = MAX(id) FROM #tr
SET @TRCounter = 1
WHILE @TRCounter <= @TRMax
BEGIN
SELECT @TRCommand = definition FROM #tr WHERE id = @TRCounter
EXEC sp_executesql @TRCommand
SET @TRCounter = @TRCounter+1
END
DROP TABLE #tr
--12
COMMIT
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply