1)I want to change all the constraint name in all tables.Please tell me.

 2)what is the Difference between Table level and column level Constraints.

 

1) To change all the constraints names for all tables would be quite a large job, and is hard to automate since you may need to name the constraints appropriately.

You can use the sp_rename system stored prcoedure to do the renaming. To get a list of constraints in your database you could use

• the sysconstraints system table (SQL Server 2000)
• or sys.foreign_keys, sys.keys, sys.check_constraints and sys.default_constraints system catalog views in SQL Server 2005

2) When constraints for individual columns are specified as part of the column specification they're known as column-level constraints. When specified for groups of columns as part of the table definition they're known as table-level constraints:

---

Gogula G. Aryalingam (MVP - SQL Server)

I want to ask who is beficial table level or column level constraints and what is the main difference. can we define both on 1 column.?

Hi Manish,

Witht eh limited information, we can guide you. 

You can use  sp_rename if you wan to do for a specific constraint.   This is the syntax

sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name' 
    [ , [ @objtype = ] 'object_type' ] 

 'Object_type'  is 'constraint'

 

I dont know what will be the new name,  I assume that you want to follow some naming convention.  Uou can get the list of constraints from sysobjects to get information over most of the constraints.

SELECT 'EXEC sp_Rename '''+name +''', '''+xtype+'_'+name +''', ''CONSTRAINT''' FROM sysobjects Where xtype in ('C', 'D', 'PK', 'UQ', 'FK')

Get he results and execute them  :)

xtype in sysobjects will show the type of constraint.

C - Check Constraint

D - Default constraint

PK - Primary  key

UQ - Unique key

FK - Foreign key

 

The difference between table constraints and column constraints are discussed mainly with Check constraints as there could be table level check constraints and column level check contraints.

PK , UQ and FK are table level constraints. Default is anyway for a column and is considered as column constraint

 

 

 

 

---

G.R.Preethiviraj Kulasingham MCITP: DBA
http://preethiviraj.blogspot.com/
Plan Your Work and Work Your Plan!

I have read from 1 site stating this.............

Is this true...........

But if you have to have a primary key and foreign key constraint for the multiple columns then you can not use this constraint in column level thse constraints are to be defined at table level.

 

 

With Regards

Manish