473,322 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

removing constraints without knowing the constraint name

I have the need to remove a constraint on a table since I'm trying to alter
the datatype of one of the columns. I know I can drop the constraint given
the name, but since the name is auto generated (something like
DF__WHRPT_ITV__Expor__45F365D3)
I need a way to find this constraint name so that I can programmatically
remove it.I can get the name using sp_helpconstraint on the table, but can't
seem to locate where the actual constraint_name is stored.

This is on SQL 2000.

Any help is appreciated.

Thanks,

-Gary
Jul 20 '05 #1
2 6552
You can get rid of Defaults with automatically named constraints in a script
with the following bit of code. Just replace the <table name> and <column
names> with your table and column(s):

DECLARE @constraint_name SYSNAME

-- remove all the defaults
WHILE 1=1
BEGIN
SET @constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))

IF @constraint_name IS NULL BREAK

EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @constraint_name)
END
--
Jacco Schalkwijk
SQL Server MVP
"Gary" <gl*@hotmail.com> wrote in message
news:EIJmd.349072$wV.15308@attbi_s54...
I have the need to remove a constraint on a table since I'm trying to alter
the datatype of one of the columns. I know I can drop the constraint given
the name, but since the name is auto generated (something like
DF__WHRPT_ITV__Expor__45F365D3)
I need a way to find this constraint name so that I can programmatically
remove it.I can get the name using sp_helpconstraint on the table, but
can't
seem to locate where the actual constraint_name is stored.

This is on SQL 2000.

Any help is appreciated.

Thanks,

-Gary

Jul 20 '05 #2
Thanks. That works perfectly.

-Gary

"Jacco Schalkwijk" <ja****************@to.newsgroups.mvps.org.invalid > wrote
in message news:uX**************@TK2MSFTNGP14.phx.gbl...
You can get rid of Defaults with automatically named constraints in a script with the following bit of code. Just replace the <table name> and <column
names> with your table and column(s):

DECLARE @constraint_name SYSNAME

-- remove all the defaults
WHILE 1=1
BEGIN
SET @constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))

IF @constraint_name IS NULL BREAK

EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @constraint_name)
END
--
Jacco Schalkwijk
SQL Server MVP
"Gary" <gl*@hotmail.com> wrote in message
news:EIJmd.349072$wV.15308@attbi_s54...
I have the need to remove a constraint on a table since I'm trying to alter the datatype of one of the columns. I know I can drop the constraint given the name, but since the name is auto generated (something like
DF__WHRPT_ITV__Expor__45F365D3)
I need a way to find this constraint name so that I can programmatically
remove it.I can get the name using sp_helpconstraint on the table, but
can't
seem to locate where the actual constraint_name is stored.

This is on SQL 2000.

Any help is appreciated.

Thanks,

-Gary


Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Olaf Meyer | last post by:
I'm wondering if I can also express uniquness constraints on attributes of objects that I have referenced from somewhere else. To be a bit more precise here a short and simple XML document. The...
2
by: DW | last post by:
Greetings: I have to do a one-off forceful change of some data in a database. I need to disable some FK constraints, make the data change, and then re-enable the constraints. My process will...
13
by: Bob Stearns | last post by:
Why is the following constraint invalid? I want to make sure that every row in IS3.ANIMALS_PRIV_INDEXES matches one of those in IS3.table_var_defn with part of the primary key fixed. Since this is...
8
by: wespvp | last post by:
I am using PostgreSQL 7.4.1 on RedHat 7.2. The query I am executing is something like (I replaced all the return values with 'count'): db=> explain select count(*) from messages m join (select...
3
by: David Parker | last post by:
I would like to be able to truncate all of the tables in a schema without worrying about FK constraints. I tried issuing a "SET CONSTRAINTS ALL DEFERRED" before truncating, but I still get constraint...
6
by: Emin | last post by:
Dear Experts, When I use a single table I can easily use constraints to enforce my business logic, but what do I do when I normalize a single table into multiple tables. For example, imagine...
4
by: Peter | last post by:
I am interested in informed feedback on the use of Constraints, Primary Keys and Unique. The following SQL statement creates a Bands tables for a database of bookings Bands into Venues, where the...
5
by: yeoj13 | last post by:
Hello, I have a db2load script I'm using to populate a large table. Ideally, my target table is required to have "Not Null" constraints on a number of different columns. I've noticed a ...
0
by: okonita | last post by:
DB2v8.2 LUW. Unable to drop Foreign Key constraints...although DROP constraints returns undefined name. Am I missing somrthing here? Hi all, I have a perplexing DB2 Drop command situation....
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.