Scripting Primary & Foreign Keys Only

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Scripting Primary & Foreign Keys Only

Author

Topic

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-10-28 : 13:57:08

Be warned, index padding is not included in this, and I'm not sure the fillfactor setting is correct.

set nocount on
create table #PK(constraint_schema sysname not null, constraint_name sysname not null, sql varchar(4000) not null, constraint PK_#PK primary key clustered(constraint_schema, constraint_name))
create table #cols(constraint_schema sysname not null, constraint_name sysname not null, column_name sysname not null, ordinal_position int not null, constraint PK_#PKcol primary key clustered(constraint_schema, constraint_name, ordinal_position))
create table #FK(constraint_schema sysname not null, constraint_name sysname not null,
unique_constraint_schema sysname not null, unique_constraint_name sysname not null,
sql varchar(4000) not null, constraint PK_#FK primary key clustered(constraint_schema, constraint_name))

insert into #PK
select constraint_schema, constraint_name, 'ALTER TABLE ' + quotename(table_schema) + '.' + quotename(TABLE_NAME) +
' ADD CONSTRAINT ' + quotename(CONSTRAINT_NAME) +
' PRIMARY KEY ' + CASE WHEN si.indid<>1 THEN 'NON' ELSE '' END +
'CLUSTERED (>cols<) WITH FILLFACTOR=' + cast(si.OrigFillFactor as varchar) + ' ON ' + quotename(fg.groupname)
AS SQL
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN sysindexes si on TC.CONSTRAINT_NAME=si.name
inner join sysfilegroups fg on si.groupid=fg.groupid
WHERE CONSTRAINT_TYPE IN('PRIMARY KEY','UNIQUE')

insert into #fk
select c.constraint_schema, c.constraint_name, c.unique_constraint_schema, c.unique_constraint_name,
'ALTER TABLE ' + quotename(F.table_schema) + '.' + quotename(F.table_name) +
' ADD CONSTRAINT ' + quotename(F.constraint_name) +
' FOREIGN KEY(>cols<) REFERENCES ' + quotename(r.table_schema) + '.' + quotename(r.table_name) +
'(>rcols<)'
AS sql
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS F
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C ON F.constraint_schema=C.constraint_schema AND f.constraint_name=c.constraint_name AND F.constraint_type='FOREIGN KEY'
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS R ON R.constraint_schema=C.unique_constraint_schema AND r.constraint_name=c.unique_constraint_name AND r.constraint_type in ('PRIMARY KEY','UNIQUE')
ORDER BY F.table_name, r.table_name

insert into #cols
select constraint_schema, constraint_name, COLUMN_NAME, ORDINAL_POSITION from INFORMATION_SCHEMA.KEY_COLUMN_USAGE

declare @ctr int, @max int, @delim varchar(1)
select @ctr=1, @max=max(ordinal_position), @delim='' from #cols

set nocount on
while @ctr<=@max
BEGIN

update P SET SQL=Replace(SQL, '>cols<', @delim + quotename(c.column_name) + '>cols<')
FROM #PK P INNER JOIN #cols C ON P.constraint_schema=C.constraint_schema AND P.constraint_name=C.constraint_name
WHERE C.ORDINAL_POSITION=@ctr

UPDATE F SET SQL=Replace(Replace(SQL, '>cols<', @delim + quotename(c.column_name) + '>cols<'), '>rcols<', @delim + quotename(r.column_name) + '>rcols<')
FROM #FK F INNER JOIN #cols C ON F.constraint_schema=C.constraint_schema AND F.constraint_name=C.constraint_name AND C.ordinal_position=@ctr
INNER JOIN #cols R ON F.unique_constraint_schema=R.constraint_schema AND F.unique_constraint_name=R.constraint_name AND C.ordinal_position=R.ordinal_position

select @ctr=@ctr+1, @delim=','
END
set nocount on

update #PK SET SQL=Replace(SQL, '>cols<', '')
update #FK SET SQL=Replace(Replace(SQL, '>cols<', ''), '>rcols<', '')

select sql from #PK
select sql from #FK

drop table #pk
drop table #fk
drop table #cols

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-29 : 17:36:18

This was a really good stuff !!!

Also a very good script can be found here: Script Indexes+PK+FK

rockmoose

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-04 : 11:42:18

Anyone want to note that there's a problem with the INFORMATION_SCHEMA Views

Brett

8-)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-04 : 11:44:13

How so Brett?
Could You be more specific.

rockmoose

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-04 : 12:13:31

Try adding some tables with different owners

Had to build my own KEY_COLUMN_USAGE...and in 7 both that and REFERENTIAL_CONSTRAINTS is wrong....

Brett

8-)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-04 : 13:40:36

Have used tables with different owners before without any problems.
( not extensive testing mind you )

The view code looks ok on first inspection, ms uses: user_name(sysobjects.uid)

Do you have an example ?

Edit:
Also the 7 stuff sounds bad, especially since I base some codegenereation on IS views. I will have to try to recreate the problem.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources