Various constraints in SQL Server and methods of querying various constraints using sql are introduced in the paper. Based on the above, table structure is implemented in this paper.
(Contains constraints) The storage process of replication. The stored procedure is tested on SQL Server 2008 and SQL Server 2014. sql is as follows
/************************************************************
* Table structure replication (with constraints) stored procedures
* Time: 2017/7/23 19:54:38
************************************************************/
if object_id(N'sp_copy_table' ,N'P') is not null
drop procedure sp_copy_table;
go
create procedure sp_copy_table
@srcTableName varchar(200),
@dstTableName varchar(220)
as
set nocount on
begin try
-- If the source table does not exist, an exception is thrown
declare @tabID varchar(30) = object_id(@srcTableName ,N'U');
if @tabID is null
raiserror('src table not exists' ,16 ,1);
-- If the target table already exists, an exception is thrown
if object_id(@dstTableName ,N'U') is not null
raiserror('destination table already exists' ,16 ,1);
-- Create tables (do not copy data)
declare @createSql varchar(max) = '';
set @createSql = 'SELECT * INTO ' + @dstTableName + ' FROM ' + @srcTableName
+ ' WHERE 1 > 1';
exec (@createSql);
-- ============== Adding constraints ================
--
-- ============= 1. unique constraint / primary constraint =============
declare @tb1 table
(IdxName varchar(255) ,colName varchar(255) ,consType tinyint)
declare @tb2 table
(IdxName varchar(255) ,colName varchar(255) ,consType tinyint)
-- Primary key constraints and unique constraints for querying the original table(Unified constraints may work on multiple columns, with the result set as multiple columns)
insert into @tb1
select idx.name as idxName
,col.name as colName
,(case when idx.is_primary_key = 1 then 1
when idx.is_unique_constraint = 1 then 2 else 0 end) consType
from sys.indexes idx
join sys.index_columns idxCol
on (
idx.object_id = idxCol.object_id
and idx.index_id = idxCol.index_id
and (idx.is_unique_constraint = 1 or idx.is_primary_key = 1)
)
join sys.columns col
on (idx.object_id = col.object_id and idxCol.column_id = col.column_id)
where idx.[object_id] = @tabID
-- According to the constraint name, a set of multi-row results of the same constraint is combined into one row and written to the temporary table.
insert into @tb2
select idxName
,colsName = stuff(
(
select ',' + colName
from @tb1
where IdxName = a.idxName
and consType = a.consType for xml path('')
)
,1
,1
,''
)
,a.consType
from @tb1 a;
-- @tb1 Temporary table data is useless, delete
delete
from @tb1;
-- Loop traversal constraints to write to the target table
declare @checkName varchar(255)
,@colName varchar(255)
,@consType varchar(255)
,@tmp varchar(max);
while exists(
select 1
from @tb2
)
begin
select @checkName = IdxName
,@colName = colName
,@consType = consType
from @tb2;
-- Primary key constraint
if @consType = 1
begin
set @tmp = 'ALTER TABLE ' + @dstTableName + ' ADD CONSTRAINT '
+ @checkName + '_01'
+ ' PRIMARY KEY (' + @colName + ')';
exec (@tmp);
end-- Unique constraint
else
if @consType = 2
begin
set @tmp = 'ALTER TABLE ' + @dstTableName + ' ADD CONSTRAINT '
+ @checkName + '_01'
+ ' UNIQUE (' + @colName + ')';
exec (@tmp);
end
-- After use, delete
delete
from @tb2
where IdxName = @checkName
and colName = @colName;
end
-- ================= 2. Foreign key constraints ===================
declare @tb3 table (fkName varchar(255) ,colName varchar(255) ,referTabName varchar(255),
referColName varchar(255))
-- Query source table foreign key constraints and write to temporary table
insert into @tb3
select fk.name as fkName
,SubCol.name as colName
,oMain.name as referTabName
,MainCol.name as referColName
from sys.foreign_keys fk
join sys.all_objects oSub
on (fk.parent_object_id = oSub.object_id)
join sys.all_objects oMain
on (fk.referenced_object_id = oMain.object_id)
join sys.foreign_key_columns fkCols
on (fk.object_id = fkCols.constraint_object_id)
join sys.columns SubCol
on (oSub.object_id = SubCol.object_id and fkCols.parent_column_id = SubCol.column_id)
join sys.columns MainCol
on (oMain.object_id = MainCol.object_id
and fkCols.referenced_column_id = MainCol.column_id)
where oSub.[object_id] = @tabID;
-- Traverse through each foreign key constraint and write to the target table
declare @referTabName varchar(255)
,@referColName varchar(255);
while exists(
select 1
from @tb3
)
begin
select @checkName = fkName
,@colName = colName
,@referTabName = referTabName
,@referColName = referColName
from @tb3;
set @tmp = 'ALTER TABLE ' + @dstTableName + ' ADD CONSTRAINT '
+ @checkName + '_01'
+ ' FOREIGN KEY (' + @colName + ') REFERENCES '
+ @referTabName + '(' + @referColName + ')';
exec (@tmp);
delete
from @tb3
where fkName = @checkName;
end
-- =============== 3.CHECK constraint ===================
declare @tb4 table (checkName varchar(255) ,colName varchar(255) ,
definition varchar(max));
insert into @tb4
select chk.name as checkName
,col.name as colName
,chk.definition
from sys.check_constraints chk
join sys.columns col
on (chk.parent_object_id = col.object_id and chk.parent_column_id = col.column_id)
where chk.parent_object_id = @tabID
-- Travel through each CHECK Constraints, adding constraints to the target table
declare @definition varchar(max);
while exists(
select 1
from @tb4
)
begin
select @checkName = checkName
,@colName = colName
,@definition = [definition]
from @tb4;
set @tmp = 'ALTER TABLE ' + @dstTableName + ' ADD CONSTRAINT '
+ @checkName + '_01'
+ ' CHECK ' + @definition;
exec (@tmp);
delete
from @tb4
where checkName = @checkName;
end
-- ================ 4. default constraint =====================
insert into @tb4
select df.name as checkName
,c.name as colName
,df.definition
from sys.default_constraints df
join sys.[columns] as c
on df.parent_column_id = c.column_id
and df.parent_object_id = c.[object_id]
where df.parent_object_id = @tabID;
-- Travel through each default constraint
while exists(
select 1
from @tb4
)
begin
select @checkName = checkName
,@colName = colName
,@definition = [definition]
from @tb4;
set @tmp = 'ALTER TABLE ' + @dstTableName + ' ADD CONSTRAINT '
+ @checkName + '_01'
+ ' DEFAULT ' + @definition + ' FOR ' + @colName;
print 'default: ' + @tmp;
exec (@tmp);
delete
from @tb4
where checkName = @checkName;
end
end try
begin catch
-- Output error message
select error_number() as ErrorNumber
,error_severity() as ErrorSeverity
,error_state() as ErrorState
,error_procedure() as ErrorProcedure
,error_line() as ErrorLine
,error_message() as ErrorMessage
end catch
Once the stored procedure is defined, it can be called to replicate the table structure. Here we use the Schedule table of ReportServer database for testing.
use ReportServer
exec dbo.sp_copy_table
@srcTableName = '[dbo].Schedule'
,@dstTableName = '[dbo].Schedule_bak'
exec sp_help 'dbo.Schedule_bak'
Notes:
1. Because some system tables of the database are used in the storage process, the data of the system tables in different databases are inconsistent.
The stored procedure does not support replicating table structures across databases. Make sure that the stored procedure is newly created in the database where table replication is required.
Re-invoking stored procedures for table structure replication
2. When the Schedule table of the ReportServer database is replicated with table structure, the following warnings will be given:
Warning! Maximum key length is 900 bytes. The maximum length of index'IX_Schedule_01'is 1040 bytes. For some large value combinations, the insert/update operation will fail.
This is due to the fact that the unique constraint of the Schedule table contains two varchar columns of 520 length, resulting in the maximum length exceeding the maximum length of the key 900.