August 5, 2014 at 8:37 pm
Comments posted to this topic are about the item Code For Truncating Table with foreign Key Constraints
Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature
October 10, 2014 at 6:29 am
hello friend, your query is very good and the problem I found that if you have more than one column in the relationship of error there. follows a version I wrote that le all columns.
declare @criavarchar(max)
select @nomeTabela = 'nametable'
if object_id('tempdb..#tmp') is not null
drop table #tmp
select o2.name ,
'[' + SCHEMA_NAME(o1.schema_id) + '].[' + o1.name + ']' as Referencing_Object_name
, s.name as Constraint_name
, c1.name as referencing_column_Name
, '[' + SCHEMA_NAME(o2.schema_id) + '].[' + o2.name + ']' as Referenced_Object_name
, c2.name as Referenced_Column_Name
, fk.keyno as orderKey
, '[' + SCHEMA_NAME(o1.schema_id) + '].[' + s.name + ']' Constraint_name_schema
into #tmp
from sysforeignkeys fk
inner join sys.objects o1 on fk.fkeyid = o1.object_id
inner join sys.objects o2 on fk.rkeyid = o2.object_id
inner join sys.columns c1 on c1.object_id = o1.object_id and c1.column_id = fk.fkey
inner join sys.columns c2 on c2.object_id = o2.object_id and c2.column_id = fk.rkey
inner join sys.objects s on fk.constid = s.object_id
and o2.name=@nomeTabela -- this predicate for a specific table
order by 1, fk.keyno
select @drop = (
select distinct
'||IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'''+ Constraint_name_schema + ''') AND parent_object_id = OBJECT_ID(N''' + Referencing_Object_name + '''))' + '||' +
'ALTER TABLE ' +
Referencing_Object_name +
' DROP CONSTRAINT ' +
Constraint_name
from #tmp o1 FOR XML PATH('')
)
select @cria = (
select distinct
'||ALTER TABLE ' +
Referencing_Object_name +
' WITH NOCHECK ADD CONSTRAINT ' +
Constraint_name +
' FOREIGN KEY ' +
'(' +
STUFF(( SELECT
'],[' + c1.referencing_column_Name
FROM #tmp c1
where c1.Referencing_Object_name = o1.Referencing_Object_name
group by c1.referencing_column_Name,orderKey
order by orderKey
FOR XML PATH('')
), 1, 2, '') + ']'
+ ')' + '||' +
' REFERENCES ' +
Referenced_Object_name +
'(' +
STUFF(( SELECT
'],[' + c1.Referenced_Column_Name
FROM #tmp c1
where c1.Referenced_Object_name = o1.Referenced_Object_name
group by c1.Referenced_Column_Name,orderKey
order by orderKey
FOR XML PATH('')
), 1, 2, '') + ']'
+ ')'
from #tmp o1 FOR XML PATH('') )
set @cria = replace(@cria,'||',char(13) + char(10))
set @drop = replace(@drop,'||',char(13) + char(10))
print (@drop)
print (@cria)
October 10, 2014 at 8:56 am
I didn't find any error, Will you show me complete error-
This will help others too.
Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature
October 10, 2014 at 10:00 am
When you have a foreign key with more than one column it creates several scripts to create the same foreign key, and not common but here in the company where I work the model was created that way to test can use the script below.
create table table_01 (cod int not null, dat datetime not null, nom varchar(10) )
alter table table_01 add primary key (cod, dat)
create table table_02 (id int identity,cod int not null, dat datetime not null, nom varchar(10) , work varchar(10) )
ALTER TABLE table_02 ADD FOREIGN KEY (cod,dat) REFERENCES table_01(cod,dat)
ALTER TABLE [table_02] DROP CONSTRAINT [FK__table_02__689D8392]
ALTER TABLE [table_02] DROP CONSTRAINT [FK__table_02__689D8392]
ALTER TABLE [table_02] ADD CONSTRAINT [FK__table_02__689D8392] FOREIGN KEY(dat)REFERENCES [table_01](dat)
ALTER TABLE [table_02] ADD CONSTRAINT [FK__table_02__689D8392] FOREIGN KEY(cod)REFERENCES [table_01](cod)
October 10, 2014 at 10:09 am
Thank you dear, I will get back to you on monday.
Happy weekend.
Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature
May 21, 2015 at 1:48 pm
The title sounds great. I need to pore over the code to see if it will work for me. Thanks.
April 11, 2016 at 6:49 am
Cool script, thanks.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply