Dynamic script

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:546732
I want to write a script for a table 'A'. I
1) want to drop all the foreign key constraints which are created in another
table(s) and in which table 'A' is being used. I dont know those foregin key
constraints while executing the script.
2) want to drop all foreign key constraints which are created in table 'A'.
I dont know those foregin key constraints while executing the script.
Is it possible?
ThanksThere is no builtin tool/script. However, you could use this as a starting
point to construct your 'drop constraint' statements.
if object_id('usp_findreferences','p') is not null
drop proc usp_findreferences
go
create procedure usp_findreferences
/ ****************************************
***********************************
*/
/* Purpose: A quick & dirty way to find ref. objects for a[ll] table[s] */
/* Author: OJ Ngo */
/* Date: 02/28/2002 */
/ ****************************************
***********************************
*/
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
exec usp_findreferences
-oj
"Rizwan" <hussains@.pendylum.com> wrote in message
news:LYuMe.7394$7R.474488@.news20.bellglobal.com...
>I want to write a script for a table 'A'. I
> 1) want to drop all the foreign key constraints which are created in
> another table(s) and in which table 'A' is being used. I dont know those
> foregin key constraints while executing the script.
> 2) want to drop all foreign key constraints which are created in table
> 'A'. I dont know those foregin key constraints while executing the script.
> Is it possible?
> Thanks
>