Constraints with alphanumeric - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Constraints with alphanumeric

Author

Topic

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-04-10 : 11:19:23

Hi experts,

I am executing a script to add a column to few tables and drop existing constraints, create new foreign key constraints, indexes etc. After we test this on our development database we have to deliver the script to the customer.
My concern here is, when I have to drop the constraint with the name, I am not able to do it because its been created with alphanumeric char in the constraint name. So I end up getting the constraint name for each table. And moreover,
I wont be able to deliver the same script to the customer as the alphanumeric in the constraint names on the customer's database will be different.

Any suggestions to overcome these alphanumeric in the constraint names?

Thanks much

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-04-10 : 11:26:06

Just explicitly name the constraints in your scripts ??

- Jeff
http://weblogs.sqlteam.com/JeffS

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-04-10 : 11:30:46

Yes, Jeff. I will name the constraints when I create them. But there are a few cases where I have to drop the existing constraints first before recreating them. And the previously created constraints by someone has alphanumeric added to the name. It will be the same case on the customer's database since the same script were sent before.

IS there a way I could drop these constraints without getting the alphanumeric from each table?

Thank you.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-10 : 11:38:31

you can discover the names by querying the system catalogs

elsasoft.org

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-04-10 : 11:48:06

This is why you should never let SQL Server name your database objects, especially in an environment where you are supporting customer databases that you do not have access to.

CODO ERGO SUM

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-04-10 : 11:51:57

Michael,

I just joined this company a month ago and I guess somebody who worked with sql server before didnt create constraints with explicit names.

Jezemine, how do I query the system catalogs??

Thank you

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 12:00:26

You can query INFORMATION_SCHEMA.TABLE_CONSTRAINTS catalog view.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-04-10 : 12:29:32

Harsh,

I used the below query, but the INFORMATION_SCHEMA.TABLE_CONSTRAINTS does not have the column name. So the query gives me the constraints from the entire table and also, it does not have constraints with df%.. Only has check,foreignkey and primary key constraints

SELECT constraint_name,constraint_type FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE table_name = 'cfsel' and constraint_name like 'DF__cfsel__cmp%';

I am trying to drop the constraint from the select statement's output(constraint). So, when I send the script to the customer, it will drop the corresponding constraint for that column.

Thank you

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-10 : 12:45:39

this will give you all the default names and the tables/columns they are applied to:


select   
    so1.name as parent  
    ,sc.name as columnname  
    ,so2.name as defaultname  
from syscolumns sc 
join sysobjects so1 on so1.id=sc.id 
left join sysobjects so2 on so2.id=sc.cdefault 
where so2.id is not null

elsasoft.org

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 12:46:06

If you are using SQL 2000, you can query system tables like this:

select object_name(constid) constraint_name, object_name(r.id) as Table_name, c.name as column_name 
from sysconstraints r join syscolumns c on r.colid = c.colid and r.id = c.id
where object_name(r.id) = 'cfsel' and object_name(constid) like 'DF_%'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

X002548
Not Just a Number

15586 Posts

Posted - 2008-04-10 : 12:47:26

DROP DATABASE

should fix everything

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 12:49:22

quote:
Originally posted by jezemine

this will give you all the default names and the tables/columns they are applied to:

select
so1.name as parent
,sc.name as columnname
,so2.name as defaultname
from syscolumns sc
join sysobjects so1 on so1.id=sc.id
left join sysobjects so2 on so2.id=sc.cdefault
where so2.id is not null

elsasoft.org

Much more elegant!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-10 : 12:50:40

leave off the where clause on that one to see all columns, regardless of whether they have a default or not.

elsasoft.org

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-04-10 : 12:56:24

Thanks a lot all!!! That helped me for df% constraints.

Is it possible to drop constraint from a select querylike in one statement? I tried the below but didnt work..

alter table cach drop constraint where
SELECT constraint_name,
constraint_type
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE table_name = 'cach' and constraint_name like 'PK_CACH_%'

Any clue on this?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 13:01:17

No you can't but you can generate ready to use ALTER TABLE statement.

Something like this:

Select 'Alter Table ' + table_name + ' drop constraint ' + constraint_name
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where constraint_type = 'primary key'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-10 : 13:01:37

no, you can't do that. you'll have to select the name of the constraint into a variable and then execute a dynamic sql statement to drop the constraint:

exec('alter table foo drop constraint ' + quotename(@myconstraint))

EDIT: we are sniping each other.

elsasoft.org

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-04-10 : 17:18:44

Jezemine, not sure how to do that, do I have to select the name of the constraint into a variable from the select statement like the one below

SELECT constraint_name,constraint_type
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE table_name = 'cach' and constraint_name like 'PK_CACH_%'

and then execute this?

exec('alter table foo drop constraint ' + quotename(@myconstraint))

Thanks

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-10 : 17:32:51

declare @myconstraint sysname

select @myconstraint=constraint_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE table_name = 'cach' and constraint_name like 'PK_CACH_%'

exec('alter table cach drop constraint ' + quotename(@myconstraint))

elsasoft.org

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-04-11 : 08:51:12

Hi jezemine, thank you for your help. I am getting syntax error for 'quotename'

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-11 : 09:04:39

You can't do concatenation in EXEC() call. Build query string outside in a variable and pass it to EXEC().

Declare @query varchar(5000)

set @query = 'alter table cach drop constraint ' + quotename(@myconstraint)
Exec(@query)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-11 : 09:36:26

or

declare @myconstraint sysname

select @myconstraint=quotename(constraint_name)
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE table_name = 'cach' and constraint_name like 'PK_CACH_%'

exec('alter table cach drop constraint ' + @myconstraint)

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources