1: DECLARE @constraintName varchar(255),@table nvarchar(50), @sql nvarchar(255), @constraintpattern nvarchar(10)
2: set @table = 'tb_Tips'
3: set @constraintpattern = '%categ%'
4:
5: --Remove foreign key constraint for tb_Tips
6: select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = @table
7: select @constraintName = CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = @table and CONSTRAINT_NAME like @constraintpattern and CONSTRAINT_TYPE = 'FOREIGN KEY'
8: select @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + @constraintName
9: exec sp_executesql @sql
10:
11: --Remove primary key constraint for table tb_Categories
12: set @table = 'tb_Categories'
13: select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = @table
14: select @constraintName = CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = @table and CONSTRAINT_NAME like @constraintpattern and CONSTRAINT_TYPE = 'PRIMARY KEY'
15: select @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + @constraintName
16: --DEBUG: select @sql
17: exec sp_executesql @sql
18:
19: -- Extend primary key field for table tb_Categories
20: ALTER TABLE tb_Categories ALTER COLUMN Code varchar(50) not null
21:
22: -- Extend foreign key field for table tb_Tips
23: ALTER TABLE tb_Tips ALTER COLUMN Category_ref varchar(50)
24:
25: -- Add primary key for table tb_Categories
26: ALTER TABLE tb_Categories WITH NOCHECK
27: ADD CONSTRAINT PK_CategoeryCode PRIMARY KEY CLUSTERED (Code)
28: WITH (FILLFACTOR = 75, PAD_INDEX = ON)
29:
30: -- Add foreign key for table tb_Tips
31: ALTER TABLE tb_Tips WITH NOCHECK
32: ADD CONSTRAINT FK_CategoryCode FOREIGN KEY (Category_ref)
33: REFERENCES tb_Categories (Code)