SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Scripting Primary & Foreign Keys Only
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 10/28/2004 :  13:57:08  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

Sweden
3279 Posts

Posted - 10/29/2004 :  17:36:18  Show Profile  Reply with Quote
This was a really good stuff !!!

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

rockmoose

Edited by - rockmoose on 10/29/2004 17:37:16
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/04/2004 :  11:42:18  Show Profile  Reply with Quote
Anyone want to note that there's a problem with the INFORMATION_SCHEMA Views

Brett

8-)
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 11/04/2004 :  11:44:13  Show Profile  Reply with Quote
How so Brett?
Could You be more specific.

rockmoose
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/04/2004 :  12:13:31  Show Profile  Reply with Quote
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-)
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 11/04/2004 :  13:40:36  Show Profile  Reply with Quote
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

Edited by - rockmoose on 11/04/2004 13:58:41
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000