wildcards in dropping constarints

[sashi]

Hi,

Is it possible to use wildards in SQL to drop a constraint on a table?

Thanks!

[Simon Hayes]

"sashi" <sa*********@yahoo.com> wrote in message
news:e7**************************@posting.google.c om...

Hi,

Is it possible to use wildards in SQL to drop a constraint on a table?

Thanks!

No, but if you can explain what you're trying to do, and give some examples
of table and constraint names, then perhaps someone can suggest a solution.
If you want to drop a lot of constraints, then this post might help:

http://groups.google.com/groups?hl=e...ews.bluewin.ch

Simon

[sashi]

Here are more details for what I am looking for:

I have a constraint on a table "Series" , and it has the following
name :
"FK__SERIES__issue_id__59C55456"

To remove the constraint, I would use a statement like :
"alter table series drop constraint FK__SERIES__issue_id__59C55456"

I want to know if there is some way to use wildcards ('%', '_' etc) to
write a statement that would achieve a similar result to that above,
but without having to give the entire key name, but by just specifying
a shorter form of the key using wildcards.

[Erland Sommarskog]

sashi (sa*********@yahoo.com) writes:

Here are more details for what I am looking for:

I have a constraint on a table "Series" , and it has the following
name :
"FK__SERIES__issue_id__59C55456"

To remove the constraint, I would use a statement like :
"alter table series drop constraint FK__SERIES__issue_id__59C55456"

I want to know if there is some way to use wildcards ('%', '_' etc) to
write a statement that would achieve a similar result to that above,
but without having to give the entire key name, but by just specifying
a shorter form of the key using wildcards.

No there isn't.

But you can say:

SELECT 'ALTER TABLE tbl DROP CONSTRAINT ' + name
FROM sysobjects
WHERE parent_obj = object_id('tbl')
AND xtype IN ('UQ', 'PK', 'F', 'D', 'C')

and then cut and paste the result.

I would also encourage you to in the future, name your constraints
explicitly, preferably with some consistent naming scheme, which
makes these dropping operations a little easier.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp