Need to Generate Script for Foreign Keys
-
Hi,
Is it possible to generate the scripts for Foreign Keys. Right now i am generating the script for tables and copying the stuff from there. 🙁 its a tedious process. Can any one help me ?
Thanks in Advance
jeswanth
--------------------------------
-
testing the email .
--------------------------------
-
Success
Back to your question:
Probably the easiest solution is to check the appropriate option in EM when you click on "Generate Script"
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Frank
Thank you for the immediate reply. But that will create tables also. I want only Foreign Key Script.
Jeswanth
--------------------------------
-
Yes, after posting my reply I became aware of it. Sorry, no good idea other than the one you already had. Maybe this can be done via SQL-DMO, but I don't know SQL-DMO.
Btw, interesting autosignature
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Ok frank. Let me check SQL DMO. Wrote some thing using that some time back.
btw i have nothing against Budha or his followers.
--------------------------------
-
Here's a script that we use to list the hierarchy of foreign keys in a database and generate 'DROP' and 'ADD' statements. Just run it in Query Analyzer.
/******************************************************************************
This script will run through the foreign keys on tables to produce a hierarchy
of the tables in a database. Use this report when determining the order for
deleting data from tables or for loading data into table.
The heirarchy produced will be :
0 Tables that have no FK relationships at all, as either as 'parents' or
'children'
1 Tables which are at the top of the tree, and have no 'parents', only
'children'
2 Tables which have a parent at the heirarchy 1 level
3 Tables which have a parent who is a child of a top level parent
4... Tables which have a parent who is a child of a child of a top level parent
Also included is a section to generate drop constraint scripts. Scripts must be run
from lowest level to highest(...3,2,1,0). Scripts are generated in that run order.
There is another section to generate add constraint scripts. These scripts must be run
from top level down (0,1,2...). Scripts are generated in that run order.
See further comments within the script.
*******************************************************************************/
SET NOCOUNT ON
DECLARE
@intCounter INT,
@intRowCount INT
CREATE TABLE #Hierarchy
(Hierarchy INT,
Child VARCHAR(100),
Parent VARCHAR(100),
FKName VARCHAR (100))
-- Set the variables
SET @intCounter = 1
SET @intRowCount = 1
/************************************************************************************
Populate the table with all child and parent table relationships, and the name of the
foreign key relationships between them. Initially sets all records hierarchy level
to 1.
************************************************************************************/
INSERT INTO #Hierarchy
SELECT DISTINCT 1 AS 'Hierarchy', S1.name AS 'Child', SO.Name AS 'Parent', S2.Name AS 'FKName'
FROM dbo.sysforeignkeys FK
INNER JOIN dbo.sysobjects SO
ON FK.rkeyID = SO.id
INNER JOIN dbo.sysobjects S1
ON FK.fkeyID = S1.id
left JOIN dbo.sysobjects S2
on FK.constid = S2.id
/************************************************************************************
Filters through the records to update the hierarchy level based on the current level
and the existance of the parent table in the child column. This establishes the hierarchtical pattern.
1st pass
- counter set to 1
- updates hierarchy to 2 where parent exists as a child and heirarchy = 1
2nd pass
- counter set to 2
- updates hierarchy to 3 where parent exists as a child and heirarchy = 2
3rd pass
- counter set to 3
- updates hierarchy to 4 where parent exists as a child and heirarchy = 3
Continues until no more levels where @@Rowcount will be 0.
************************************************************************************/
WHILE @intRowCount <> 0
BEGIN
UPDATE #Hierarchy
SET Hierarchy = Hierarchy + 1
WHERE Hierarchy = @intCounter
AND Parent IN (SELECT DISTINCT Child
FROM #Hierarchy
WHERE Hierarchy = @intCounter)
SET @intRowCount = @@Rowcount
SET @intCounter = @intCounter + 1
END
/************************************************************************************
This next section filters through all of the records determines the hierarchy level
and inserts a reporting record for those tables that have no Foreign Key relationships
or are the top level Parent table.
************************************************************************************/
-- Add the tables that have no Foreign Key relationships
INSERT INTO #Hierarchy
SELECT -1, [name], ' No FK ', ' - '
FROM dbo.sysobjects
WHERE [name] NOT IN (SELECT DISTINCT Parent FROM #Hierarchy)
AND [Name] NOT IN (SELECT DISTINCT Child FROM #Hierarchy)
AND xtype = 'U'
-- Add the tables that are top level Parents only
INSERT INTO #Hierarchy
SELECT DISTINCT 0,' - ' , Parent, ' - '
From #Hierarchy
WHERE Parent NOT IN (SELECT Child FROM #Hierarchy)
AND Hierarchy <> -1
-- Add 1 to adjust the hierarchies to start at 0
UPDATE #Hierarchy
SET Hierarchy = Hierarchy + 1
-- Display the results
SELECT DISTINCT Hierarchy, Child, Parent, FKName
FROM #Hierarchy
ORDER BY Hierarchy, Child, Parent
-- Generates Drop Constraint Scripts
select 'ALTER TABLE ' + cast(child as char(40)) + 'DROP CONSTRAINT '
+ cast(FKName as char(50)) + char(13)
from #Hierarchy
ORDER BY Hierarchy desc, Child, Parent
-- Generates Add Constraint Scripts
select 'ALTER TABLE ' + cast(child as char(40)) + 'ADD CONSTRAINT '
+ cast(FKName as char(50)) + 'FOREIGN KEY ' + '(' + fc.name + ') ' + 'REFERENCES '
+ cast(Parent as char(30)) + '(' + rc.name + ') ' + char(13)
from (select distinct constid, fkeyid, rkeyid, fkey, rkey from sysforeignkeys) as FK
join sysobjects c on c.id = FK.constid
join sysobjects t on t.id = FK.fkeyid
join sysobjects rt on rt.id = FK.rkeyid
join syscolumns fc on fc.colid = FK.fkey and fc.id = FK.fkeyid
join syscolumns rc on rc.colid = FK.rkey and rc.id = FK.rkeyid
join #Hierarchy H1 on t.name = child and rt.name = Parent
ORDER BY Hierarchy, Child, Parent
-- Clean up
DROP TABLE #Hierarchy
Greg
-
Hi Greg,
Thanks a Lot its working. its a really good script. truely usefull.
Jeswanth
--------------------------------
-
Hi
I created one More script seems like doing the stuff.
Craete teh Following Scrtipt
/*
Purpose : To get the Column name for a specified table id and the column id
Author : Jeswanth
*/
Create Function Column_Name(@tableid int,@ColumnID smallint)
returns Varchar(4000)
as
begin
declare @column_name varchar(4000)
select @column_name = name from sysColumns where id = @tableid and colid=@ColumnID
return @column_name
end
Now Use whatever way you want to use.
select 'ALTER TABLE [dbo].[' + object_name(fkeyid) + ']'+ ' ADD CONSTRAINT [' + object_name(constid) + '] FOREIGN KEY (['+ dbo.Column_Name(fkeyid,fkey) + '])REFERENCES [dbo].[' + object_name(rkeyid) + '](['+ dbo.Column_name(rkeyid,rkey) + '])' from sysforeignkeys
There are more stuff in sysreferences. but for the time being this will do the job for me.
Jeswanth
--------------------------------
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply