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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy