Geeks With Blogs

News


Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done

List All Primary Keys and Foreign Keys

Original article:  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41896

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 order by sql
select sql from #FK order by sql

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

 

Posted on Thursday, October 23, 2008 3:49 AM SQL Server 2005 Tricks , SQL Server 2000 Tricks | Back to top


Comments on this post: List All Primary Keys and Foreign Keys

# re: List All Primary Keys and Foreign Keys
Requesting Gravatar...
flower girl dresses, tuxedos, and other special occasion apparel. Site includes a bridal
wedding dresses
Left by G on Mar 29, 2010 12:34 AM

# re: List All Primary Keys and Foreign Keys
Requesting Gravatar...
many thanks
Left by stay younger on Jun 15, 2011 9:29 PM

# re: List All Primary Keys and Foreign Keys
Requesting Gravatar...
Great info, thanks.
Left by canvas print on Jul 05, 2011 8:06 AM

Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net