I'm working on a DB that manages history tables in the following way: the "dashboard" table called, for example, MYTABLE1, and an increasing number of "copies", one for each year: MYTABLE1_BCK_2014, MYTABLE1_BCK_2015,..., MYTABLE1_BCK_2022, and so on. Same way for MYTABLE2, MYTABLE3, etc.
Then I can have N tables to duplicate each year, and the copy is made from the last backup table existing (for example, MYTABLE_BCK_2022 is created copying the structure from MYTABLE_BCK_2021, due to, along the time, the dashboard table could be changed in the structure, but not always the system needs to reflect this change on the backup table). At worst, when the dashboard table changes its structure, only the last backup table structure is updated.
Obviously, for each dashboard table, I can have a variable number of fields and field types, including float, nvarchar, varchar, datetime, etc.
I have written a script that dynamically detects which tables need to be cloned, creates a copy of each, and adds any constraint needed in this way:
CREATE OR ALTER PROCEDURE CreateBackupTables(
@ReferenceYear [int] -- Year on which to create the new backup tables
)
AS
BEGIN
DECLARE @SqlString [nvarchar] (max) = ''
DECLARE @SqlStringInsert [nvarchar] (max) = ''
DECLARE @SqlCreateTable [nvarchar] (max) = ''
DECLARE @SqlCreateView [nvarchar] (max) = ''
DECLARE @SqlCreateConstraint [nvarchar] (max) = ''
/* This table will contain the names of the tables from which to start to create the backup ones, with relative constraints for primary key */
CREATE TABLE #TableNamesToCreate(
TableName varchar(50),
ConstraintName varchar(50),
ConstraintFields varchar(40)
)
/* Read from the system tables the names of the tables on which to create the new backups, starting from the existing ones for the reference year -1 */
SET @SqlString = N'SELECT TableName, constraint_name, details
FROM
(SELECT DISTINCT SUBSTRING(t.name,1,CHARINDEX(''_BCK_' + CONVERT(NVARCHAR(4),@ReferenceYear-1) +''',t.name)-1) AS [TableName] FROM sys.tables t WHERE t.name LIKE ''%_BCK_' + CONVERT(NVARCHAR(4),@ReferenceYear-1) + ''') A
LEFT JOIN
(SELECT t.[name] AS table_view, isnull(c.[name], i.[name]) AS constraint_name,
substring(column_names, 1, LEN(column_names)-1) AS [details]
FROM sys.objects t
LEFT OUTER JOIN sys.indexes i
ON t.object_id = i.object_id
LEFT OUTER JOIN sys.key_constraints c
ON i.object_id = c.parent_object_id
AND i.index_id = c.unique_index_id
CROSS APPLY (SELECT col.[name] + '', ''
FROM sys.index_columns ic
INNER JOIN sys.columns col
ON ic.object_id = col.object_id
AND ic.column_id = col.column_id
WHERE ic.object_id = t.object_id
AND ic.index_id = i.index_id
ORDER BY col.column_id
FOR XML PATH ('''') ) D (column_names)
WHERE is_unique = 1
AND t.name LIKE ''%_BCK_' + CONVERT(NVARCHAR(4),@ReferenceYear-1) + '''
AND t.is_ms_shipped <> 1) B ON A.TableName = SUBSTRING(B.table_view,1,CHARINDEX(''_BCK_' + CONVERT(NVARCHAR(4),@ReferenceYear-1) +''',B.table_view)-1)
ORDER BY TableName, constraint_name'
/* Generate the data entry string on the tables to be created */
SET @SqlStringInsert = N'INSERT INTO #TableNamesToCreate ' + @SqlString
PRINT @SqlStringInsert
EXECUTE sp_executesql @SqlStringInsert
/* Verification of the tables found */
SELECT * FROM #TableNamesToCreate
/* This table will contain the details of the fields to add to each table (data type, possible length, etc.) */
CREATE TABLE #Tables(
TableName varchar(50),
ColumnId int,
ColumnName varchar(50),
ColumnType varchar(10),
ColumnLength int,
ColumnPrecision int,
ColumnNullable varchar(10)
)
/* Read the details of the columns. The fields of type nvarchar and nchar have double length, so it is necessary to divide them by 2 */
SET @SqlString = N' SELECT t.name, c.column_id, c.name, ty.name, CASE WHEN ty.name in(''nvarchar'',''nchar'') THEN c.max_length/2 ELSE c.max_length END, c.precision, c.is_nullable
FROM sys.all_columns c INNER JOIN
sys.tables t ON c.object_id = t.object_id INNER JOIN
sys.types ty ON c.user_type_id = ty.user_type_id
WHERE t.name IN (SELECT DISTINCT SUBSTRING(t.name,1,CHARINDEX(''_BCK_' + CONVERT(NVARCHAR(4),@ReferenceYear-1) +''',t.name)-1) FROM sys.tables t WHERE t.name LIKE ''%_BCK_' + CONVERT(NVARCHAR(4),@ReferenceYear-1) + ''')
ORDER BY t.name, c.column_id'
/* Enter the results in the data table */
SET @SqlStringInsert = N'INSERT INTO #Tables ' + @SqlString
PRINT @SqlStringInsert
EXECUTE sp_executesql @SqlStringInsert
/* Start preparing the table creation scripts */
DECLARE cursoreNomeTabella CURSOR FOR SELECT TableName, ConstraintFields FROM #TableNamesToCreate
DECLARE @NomeTabellaDaCreare varchar(50)
DECLARE @ConstraintFields varchar(40)
OPEN cursoreNomeTabella
FETCH NEXT FROM cursoreNomeTabella INTO @NomeTabellaDaCreare, @ConstraintFields
WHILE @@FETCH_STATUS=0
BEGIN
/* I check that the table to be created is not already present on the DB */
SET @SqlCreateTable = N'IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @NomeTabellaDaCreare + '_BCK_' + CONVERT(NVARCHAR(4),@ReferenceYear) + ']'') AND type in (N''U'')) CREATE TABLE [dbo].[' + @NomeTabellaDaCreare + '_BCK_' + CONVERT(NVARCHAR(4),@ReferenceYear) + '] ('
DECLARE @SqlCreateField [nvarchar](max) = ''
DECLARE @SqlConstraint [nvarchar](max) = ''
DECLARE @ColumnId int, @ColumnName varchar(50), @ColumnType varchar(10), @ColumnLength int, @ColumnPrecision int, @ColumnNullable varchar(10)
DECLARE cursoreCampi CURSOR FOR SELECT ColumnName, ColumnType, ColumnLength, ColumnPrecision, ColumnNullable FROM #Tables WHERE TableName = @NomeTabellaDaCreare
OPEN cursoreCampi
FETCH NEXT FROM cursoreCampi INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnPrecision, @ColumnNullable
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @ColumnLengthToChar [varchar](4) = 'max'
IF @ColumnLength != -1 AND @ColumnType NOT IN ('nvarchar','nchar') SET @ColumnLengthToChar = CONVERT(VARCHAR(4),@ColumnLength)
IF @ColumnLength = 0 AND @ColumnType IN('nvarchar','nchar') SET @ColumnLengthToChar = 'max'
IF @ColumnLength > 0 AND @ColumnType IN('nvarchar','nchar') SET @ColumnLengthToChar = CONVERT(VARCHAR(4),@ColumnLength)
SET @SqlCreateField = @SqlCreateField + @ColumnName + ' [' + @ColumnType + ']'
SET @SqlCreateField = @SqlCreateField + CASE WHEN @ColumnType NOT IN ('int','bit','bigint','date','datetime','datetime2','ntext') THEN '(' + @ColumnLengthToChar + ') ' ELSE ' ' END
SET @SqlCreateField = @SqlCreateField + CASE WHEN @ColumnNullable = 0 THEN 'NOT NULL' ELSE 'NULL' END + ', '
FETCH NEXT FROM cursoreCampi INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnPrecision, @ColumnNullable
END
SET @SqlCreateTable = @SqlCreateTable + @SqlCreateField
IF @ConstraintFields IS NOT NULL
BEGIN
SET @SqlConstraint = ' CONSTRAINT [PK_' + @NomeTabellaDaCreare + '_BCK_' + CONVERT(VARCHAR(4), @ReferenceYear) + '] PRIMARY KEY CLUSTERED (' + @ConstraintFields +
') WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]'
IF @ConstraintFields = 'ID'
SET @SqlConstraint = @SqlConstraint + ' TEXTIMAGE_ON [PRIMARY]'
SET @SqlConstraint = @SqlConstraint + ';'
IF @ConstraintFields IS NOT NULL
SET @SqlCreateTable = @SqlCreateTable + @SqlConstraint
ELSE
SET @SqlCreateTable = @SqlCreateTable + ')'
END
ELSE
BEGIN
SET @SqlCreateTable = SUBSTRING(@SqlCreateTable, 1, LEN(@SqlCreateTable)-1) + ');'
END
PRINT @SqlCreateTable
EXECUTE sp_executesql @SqlCreateTable
CLOSE cursoreCampi
DEALLOCATE cursoreCampi
FETCH NEXT FROM cursoreNomeTabella INTO @NomeTabellaDaCreare, @ConstraintFields
END
CLOSE cursoreNomeTabella
DEALLOCATE cursoreNomeTabella
/* End of insertion of tables */
/* This table will contain the views from which to start to create the new ones */
CREATE TABLE #Views(
ViewName varchar(50),
ViewDefinition nvarchar(max)
)
/* Read the details of the views */
SET @SqlString = N' SELECT REPLACE(v.name, ' + CONVERT(NVARCHAR(4),@ReferenceYear-1) + ', ' + CONVERT(NVARCHAR(4),@ReferenceYear) + ') AS [ViewName],
REPLACE(m.definition, ' + CONVERT(NVARCHAR(4),@ReferenceYear-1) + ', ' + CONVERT(NVARCHAR(4),@ReferenceYear) + ') AS [ViewDefinition]
FROM sys.views v JOIN sys.sql_modules m ON m.object_id = v.object_id
WHERE v.name LIKE ''%' + CONVERT(NVARCHAR(4),@ReferenceYear-1) + '''
ORDER BY ViewName;'
PRINT @SqlString
/* Insert the results in the view table */
SET @SqlStringInsert = N'INSERT INTO #Views ' + @SqlString
PRINT @SqlStringInsert
EXECUTE sp_executesql @SqlStringInsert
/* Start inserting views */
DECLARE cursoreViste CURSOR FOR SELECT ViewName, ViewDefinition FROM #Views
DECLARE @ViewName nvarchar(50)
DECLARE @ViewDefinition nvarchar(max)
OPEN cursoreViste
FETCH NEXT FROM cursoreViste INTO @ViewName, @ViewDefinition
WHILE @@FETCH_STATUS=0
BEGIN
SET @SqlCreateView = REPLACE(@ViewDefinition, 'CREATE','CREATE OR ALTER')
PRINT @SqlCreateView
EXECUTE sp_executesql @SqlCreateView
FETCH NEXT FROM cursoreViste INTO @ViewName, @ViewDefinition
END
CLOSE cursoreViste
DEALLOCATE cursoreViste
/* End of insertion of views */
/* This table will contain the list of constraints to add to the tables */
CREATE TABLE #Constraints(
TableName nvarchar(100),
ConstraintName nvarchar(100),
ConstraintColumn nvarchar(100),
ConstraintDefinition nvarchar(20)
)
/* Read the data of the constraints */
SET @SqlString = N' SELECT schema_name(t.schema_id) + ''.'' + REPLACE(t.[name], ' + CONVERT(NVARCHAR(4),@ReferenceYear-1) + ', ' + CONVERT(NVARCHAR(4),@ReferenceYear) + ') AS [TableName],
REPLACE(con.[name], ' + CONVERT(NVARCHAR(4),@ReferenceYear-1) + ', ' + CONVERT(NVARCHAR(4),@ReferenceYear) + ') AS [ConstraintName],
col.[name] AS [ConstraintColumn], con.[definition] AS [ConstraintDefinition]
FROM sys.default_constraints con
LEFT OUTER JOIN sys.objects t
ON con.parent_object_id = t.object_id
LEFT OUTER JOIN sys.all_columns col
ON con.parent_column_id = col.column_id
AND con.parent_object_id = col.object_id
WHERE t.name like ''%' + CONVERT(NVARCHAR(4),@ReferenceYear-1) + ''''
/* Enter the results in the constraints table */
SET @SqlStringInsert = N'INSERT INTO #Constraints ' + @SqlString
PRINT @SqlStringInsert
EXECUTE sp_executesql @SqlStringInsert
/* Start creating constraints */
DECLARE cursoreConstraints CURSOR FOR SELECT TableName, ConstraintName, ConstraintColumn, ConstraintDefinition FROM #Constraints
DECLARE @TableName nvarchar(50)
DECLARE @ConstraintName nvarchar(50)
DECLARE @ConstraintColumn nvarchar(100)
DECLARE @ConstraintDefinition varchar(20)
OPEN cursoreConstraints
FETCH NEXT FROM cursoreConstraints INTO @TableName, @ConstraintName, @ConstraintColumn, @ConstraintDefinition
WHILE @@FETCH_STATUS=0
BEGIN
SET @SqlCreateConstraint = N'IF NOT EXISTS(SELECT * FROM sys.DEFAULT_CONSTRAINTS WHERE NAME=''' + @ConstraintName + ''' ) ALTER TABLE ' + @TableName + ' ADD CONSTRAINT [' + @ConstraintName + '] DEFAULT ' + @ConstraintDefinition + ' FOR [' + @ConstraintColumn + ']'
PRINT @SqlCreateConstraint
EXECUTE sp_executesql @SqlCreateConstraint
FETCH NEXT FROM cursoreConstraints INTO @TableName, @ConstraintName, @ConstraintColumn, @ConstraintDefinition
END
CLOSE cursoreConstraints
DEALLOCATE cursoreConstraints
/* End of constraints creation */
/* Delete the temporary tables */
DROP TABLE #TableNamesToCreate
DROP TABLE #Tables
DROP TABLE #Views
DROP TABLE #Constraints
END
The script is working almost well, but there is an issue with float datatype fields, that are automatically converted in real datatype fields.
Does anyone know why this is happening and/or how to manage it?
As suggested by @SMor, I would mistrust assigning a precision (@ColumnLengthToChar) to the float datatype. It's legit syntax, but not necessary and might be causing problems? Try adding it (and "real") to this NOT IN list:
SET @SqlCreateField = @SqlCreateField + CASE WHEN @ColumnType NOT IN ('int','bit','bigint','date','datetime','datetime2','ntext') THEN '(' + @ColumnLengthToChar + ') ' ELSE ' ' END
Also, see what happens when you collect the PRINT statements of your dynamic SQL and run them manually.