SQL SERVER – 2005 – List All The Constraint of Database – Find Primary Key and Foreign Key Constraint in Database « SQL Server Journey with SQL Authority

Feeds:: Posts; Comments

« SQLAuthority News – Book Review – Pro T-SQL 2005 Programmer’s Guide (Paperback)

SQLAuthority News – Active Directory Integration Sample Script »

SQL SERVER – 2005 – List All The Constraint of Database – Find Primary Key and Foreign Key Constraint in Database

September 16, 2007 by pinaldave

Following script are very useful to know all the constraint in the database. I use this many times to check the foreign key and primary key constraint in database. This is simple but useful script from my personal archive.
USE AdventureWorks; GO SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint, SCHEMA_NAME(schema_id) AS SchemaName, OBJECT_NAME(parent_object_id) AS TableName, type_desc AS ConstraintType FROM sys.objects WHERE type_desc LIKE '%CONSTRAINT' GO
Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Constraint and Keys, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology | 76 Comments

76 Responses

on September 16, 2007 at 11:07 pm | Reply abhay

Dear Pinal,

To get the idea of any relationship between tables using foreign key relationships, I think, following query will be more useful :

select Referencing_Object_name, referencing_column_Name, Referenced_Object_name, Referenced_Column_Name from
(select Referenced_Column_Name = c.name, Referenced_Object_name = o.name, f.constid from sysforeignkeys f, sysobjects o, syscolumns c
where (f.rkeyid = o.id) and c.id = o.id and c.colid = f.rkey) r,
(select referencing_column_Name = c.name, Referencing_Object_name = o.name, f.constid from sysforeignkeys f, sysobjects o, syscolumns c
where (f.fkeyid = o.id) and c.id = o.id and c.colid = f.fkey) f
where r.Referenced_Column_Name = f.referencing_column_Name
and r.constid = f.constid
order by f.Referencing_Object_name

Regards,
Abhay
- on March 2, 2010 at 3:58 pm | Reply vaibhav
  
  Its really helpful infromation.
  Lots of hidden trissures are found in all articales of this web
  
  RELALLY GRT ONE
  - on April 27, 2011 at 3:30 pm | Reply Satish
    
    Itz good…..
on September 30, 2007 at 10:38 pm | Reply Ujjaval Suthar

Hey Pinal,

I was going through google to look for the query that you’ve posted here and came across your blog.

Its surprising to see you and remember your face from Nirma Bus, if my memory is not wrong. I was studying in Nirma between 1999 – 2003. And you are also from Gandhingar, right? yeah I am from Gandhinagar.

Regards,
Ujjaval

PS:- Oh by the way, useful post. Thanks for that.
on October 2, 2007 at 6:03 pm | Reply pinaldave

Hi Ujjaval Suthar,

Yes, I am Pinal from Gandhinagar. Nirma 1999-2003.

Regards,
Pinal
on October 23, 2007 at 7:43 am | Reply Rob

This query works and was very helpful to me. Thanks.
on December 21, 2007 at 1:11 am | Reply Shishir Khandekar

For SQL Server 2005, you can use the sys.foreign_keys view to achieve the same. The columns in this view are self explanatory so am not publishing the query here.

Regards
Shishir
on December 21, 2007 at 5:47 pm | Reply Sandeep

i’m new to sql server 2005 …
can u plz suggest ny gud book for sqlserver 2005 for a beginner and i want to do certification for d same..

plz guide!!!!!
on December 21, 2007 at 6:11 pm | Reply Sandeep

sir,
i got my answer, initially i didn’t search in sqlauthority.com!!

thx
on December 28, 2007 at 2:25 pm | Reply rajesh

Hello Sir,
I am a .net Developer and I came across some difficult questions during the interview on database. The question
is :
There is a table with 5 coloumns, in that under country coloumn the fields should be all asain countries. If the user
enters other than asian country it should throw an error.
How to write a query for this?
on January 18, 2008 at 7:21 pm | Reply Tom W

1 column should be for continent.
Put ‘Asia’ in as the value for all the Asian coutries, etc.
on February 22, 2008 at 3:20 pm | Reply szolarp

Hi!
I made an easier to understand script that shows all constraint in the database including that rows where the referencing_column_name and the referenced_column_name are different and the name of the constraint
select
o1.name as Referencing_Object_name
, c1.name as referencing_column_Name
, o2.name as Referenced_Object_name
, c2.name as Referenced_Column_Name
, s.name as Constraint_name
from sysforeignkeys fk
inner join sysobjects o1 on fk.fkeyid = o1.id
inner join sysobjects o2 on fk.rkeyid = o2.id
inner join syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkey
inner join syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkey
inner join sysobjects s on fk.constid = s.id
inner join syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkey
inner join syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkey
inner join sysobjects s on fk.constid = s.id
on March 27, 2008 at 10:44 pm | Reply Tushar Mehere

Hi szolarp,

Your query is prefect. It had some defect, i refined it below.

select
o1.name as Referencing_Object_name
, c1.name as referencing_column_Name
, o2.name as Referenced_Object_name
, c2.name as Referenced_Column_Name
, s.name as Constraint_name
from sysforeignkeys fk
inner join sysobjects o1 on fk.fkeyid = o1.id
inner join sysobjects o2 on fk.rkeyid = o2.id
inner join syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkey
inner join syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkey
inner join sysobjects s on fk.constid = s.id
and o2.name=’tblUserDetails’ — this predicate for a specific table
on March 29, 2008 at 7:42 am | Reply Aviv

Hi
Does anyone know how can I find the relationship between colomn and default constraint. I need it for dynamicly drop the column in different databases
on April 16, 2008 at 4:34 pm | Reply Rakesh Dewangan

Hi Pinal,
really this procedure its very very useful,
I was facing trouble from last half an hour, and your query has resolved my problem, with in second.

thanks a lot.

great work!!!!
on May 5, 2008 at 2:57 pm | Reply Henko

Thanks for the script above, it really helped me on deleting my DWH constraints. Cheers.
on May 12, 2008 at 5:07 pm | Reply Gerry

This blog helped me a lot! Many thanks to you all.

I was looking for days to solve my constraint problem.
this is very usefull in my ADO application. Looks like ADO does not have functions to read detailed constraint information like this script from an SQL database. Therefore I was forced to search for a solution in SQL scripting and here it was!

Thx!
on May 24, 2008 at 6:16 pm | Reply Niraimathi

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the REFERENCE constraint “hrpyprc_ps_auth_r01_fk”. The conflict occurred in database “HRMS40_CBI”, table “dbo.hrpyprc_payset_auth_cnt”.
The statement has been terminated.
on June 23, 2008 at 2:41 pm | Reply Tim4it

szolarp & Tushar

Many thanks .. you really helped me ..
on August 4, 2008 at 5:00 pm | Reply kalyan

i’m new to sql server 2005 …
can u plz suggest ny gud book for sqlserver 2005 for a beginner and i want to do certification for d same..

plz guide!!!!!
on August 7, 2008 at 4:47 pm | Reply Ankush

What r the differences b/w Sql Server 2005 and 2000
on August 26, 2008 at 10:04 am | Reply Muhammad Usman Arshad

Hi,

I think using information schema is much useful than using sysobjects. To see the list of constraints you can use the query:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

or you can use database name with it:

SELECT * FROM [SugarCMS].INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Here is the complete list of information_schema views:

CHECK_CONSTRAINTS: Check Constraints
COLUMN_DOMAIN_USAGE: Every column that has a user-defined data type.
COLUMN_PRIVILEGES: Every column with a privilege granted to or by the current user in the current database.
COLUMNS:Lists every column in the system
CONSTRAINT_COLUMN_USAGE: Every column that has a constraint defined on it.
CONSTRAINT_TABLE_USAGE: Every table that has a constraint defined on it.
DOMAIN_CONSTRAINTS: Every user-defined data type with a rule bound to it.
DOMAINS: Every user-defined data type.
KEY_COLUMN_USAGE: Every column that is constrained as a key
PARAMETERS: Every parameter for every user-defined function or stored procedure in the datbase. For functions this returns one row with return value information.
REFERENTIAL_CONSTRAINTS: Every foreign constraint in the system.
ROUTINE_COLUMNS: Every column returned by table-valued functions.
ROUTINES: Every stored procedure and function in the database.
SCHEMATA: Every database in the system.
TABLE_CONSTRAINTS: Every table constraint.
TABLE_PRIVILEGES: Every table privilege granted to or by the current user.
TABLES: Every table in the system.
VIEW_COLUMN_USAGE: Every column used in a view definition.
VIEW_TABLE_USAGE: Every table used in a view definition.
VIEWS: Every View
- on February 23, 2010 at 5:48 pm | Reply Divya
  
  it is so useful…… :)
on August 29, 2008 at 10:27 pm | Reply Syed

Hi,
Does any one know how to get DEFAULT Constraint along with the name of the column on which it has defined.
Nirmal query gives the table name, but not the column name, because most of the time DEFAULT is created on the column by using DEFAULT, which create the system generated name of the DEFAULT, so I would like to know how to get the column name along with the table name.

I would appreciate any suggestion.

Thanks
on August 30, 2008 at 3:44 am | Reply Imran Mohammed

@SYED,

This might help,

SELECT OBJECT_NAME(PARENT_OBJECT_ID) TABLE_NAME,
COL_NAME (PARENT_OBJECT_ID, PARENT_COLUMN_ID)COLUMN_NAME ,
NAME DEFAULT_CONSTRAINT_NAME
FROM SYS.DEFAULT_CONSTRAINTS ORDER BY 1

Hope this helps,
Imran.
on September 12, 2008 at 10:51 pm | Reply Arthur

I think is easier… it’s pretty much like the one publish by szolarp and “fixed” by Tushar Mehere…

is this:
SELECT RO.NAME AS ParentTable, RC.NAME AS ParentColumn, FO.NAME AS ForeignTable, FC.NAME AS ForeignColumn
FROM sysforeignkeys F INNER JOIN sysobjects RO
ON F.rkeyid = RO.id INNER JOIN syscolumns RC
ON RC.id = RO.id AND RC.colid = F.rkey INNER JOIN sysobjects FO
ON F.fkeyid = FO.id INNER JOIN syscolumns FC
ON FC.id = FO.id AND FC.colid = F.fkey
ORDER BY RO.NAME, RC.NAME, FO.NAME, FC.NAME

we don’t really to know the name…
on September 27, 2008 at 4:17 am | Reply Mark

Hi Pinal,
I have a situation wherein I need to change a particular name in my entire database.
Please tell me how can i trace this particular word – it might be occuring in ‘n’ number of tables in the database, in ‘n’ number of column values and in ‘n’ number of records throughout present in any table of that database.
I need to write a cursor, go to each record and trace that word, likewise for others, for each table. I heard that sysobjects and syscolumns could help in this scenario, could you please generate the code if possible.

Thanks, Mark
on October 31, 2008 at 5:34 pm | Reply suresh

i was seaching for the query regarding constraints. It was nice and thankx
on February 5, 2009 at 8:25 am | Reply Sajid

Superb site…

This site really provides simple solutions for complex problems

Thanks alot
on February 24, 2009 at 5:40 pm | Reply SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – 2005 – List All The Constraint of Database – Find Primary Key and Foreign Key Constrain… [...]
on May 19, 2009 at 3:43 pm | Reply Rama

hey pinal

i get this error when i run ur script

‘SCHEMA_NAME’ is not a recognized function name.

ne thoughts?
on May 22, 2009 at 12:02 am | Reply Shihab

Is there way to find DEFAULT_CONSTRANTS of the table from INFORMATION_SCHEMA ?
on May 22, 2009 at 11:21 am | Reply Imran Mohammed

@Shihab

If you see, text of Information_Schema.Constraints view you will find that it is querying from sys.objects view. where we have information about all objects in the database.

I used the same view but modified it a little bit in order to get information for default constraints, here is the script.

select
Object_Id
,[name] [Default_Constraint_Name]
,schema_name (schema_id) [Schema_Name]
,object_name (parent_object_id)[Table_Name]
,[Create_Date]
,[Modify_Date]
from sys.objects where type = ‘D’

~ IM.
on July 18, 2009 at 1:20 pm | Reply padma

hi,
im a beginner..please tell me syntax how to create a table within a schema?..
on July 19, 2009 at 4:33 am | Reply Imran Mohammed

@Padma,

If Schema name is Product

And you want to create a table under schema Product, then you use this script.

Create table Product.Table1 (Eid Int, Ename Varchar(100))

Where Product is Schema Name, and Table1 is the name of the table.

You should have DDL Permissions, Permissions to create objects in the database.

IM.
on August 20, 2009 at 4:10 pm | Reply Neelam Sodhi

hi pinal,

how to insert/update a tables’s data residing in database A(suppose) of server1 to server 2′s database B’s table which can have same or different table structure.

I know one solution is with the usage of linked server,but i want to do it using replication or mirroring or any other technique(if available) for sql server 2005.

Please provide me code/querry’s rather than theory.

Thanks alot.
on February 17, 2010 at 1:43 am | Reply Siraj

Hi Pinal,

The information which you have provided is very much usefull for me in one of my database related .Net project. Thank you soo much for this post.
I am right now facing one problem I need to display the list of all the Aggregate Functions from SQL Server 2005. So, is there query from executing which I could get this expected result. Please help me….

Thank you in advance…

Thanks,
Siraj.
- on February 17, 2010 at 12:47 pm | Reply Marko Parkkola
  
  Check this out:
  
  SELECT * FROM sys.objects
  
  You’ll see that there’s a lot more than just functions listed. Use Type-field to filter out the rows that are interesting. If you need to find out CREATE clauses of those objects they are stored in sys.sql_modules.
  
  SELECT *
  FROM sys.objects o
  JOIN sys.sql_modules m on m.object_id = o.object_id
on February 17, 2010 at 8:05 am | Reply Pinal Dave

Hello Siraj,

If you want the list of SQL Server built-in aggregate functions then please visit below post:

http://blog.sqlauthority.com/2008/01/19/sql-server-introduction-to-aggregate-functions/

Otherwise if you have created CLR aggregate functions than use the below query:

SELECT *
FROM sys.all_objects
WHERE ([type] = ‘AF’)
ORDER BY [name];

Regards,
Pinal Dave
on February 19, 2010 at 8:30 pm | Reply mariadim

Pretty nice and simple.

I also found useful to show the definition of each constraint.
To do so just add the following to select statement:
OBJECT_DEFINITION(OBJECT_ID) AS Definition

Thanks for sharing.
on March 6, 2010 at 3:27 pm | Reply Vikranth

Hi Pinal,

This is very good site for SQL Server developer, i usually go thru it.

i have on question

How to script all constraint scripts of a database with out table structure’s.

Thanks
on March 23, 2010 at 2:25 pm | Reply Ravi

Hi Pinal,

I have some small doubts that is

1. what is best Database in performance for ASP.NET

2. why people don’t use XML column (My Project Manager says don’t use that.)

Can you tell me clear explanation.
on March 31, 2010 at 1:33 pm | Reply Alberto De Caro

Hi Pinal,
I’m sorry for my not good english.

I’ve also written a similar query, that I use a lot with huge databases.
select fkcn.name as fk_cnst_name, fkc.constraint_column_id as fk_cnst_col, fkt.name as fk_tbl_name, fkcl.name as fk_col_name, pkt.name as pk_tbl_name, pkcl.name as pk_col_name from sys.foreign_key_columns as fkc join sys.foreign_keys as fkcn on fkcn.object_id = fkc.constraint_object_id join sys.columns as fkcl on (fkcl.object_id = fkc.parent_object_id and fkcl.column_id = fkc.parent_column_id) join sys.tables as fkt on fkt.object_id = fkc.parent_object_id join sys.tables as pkt on pkt.object_id = fkc.referenced_object_id join sys.columns as pkcl on (pkcl.object_id = fkc.referenced_object_id and pkcl.column_id = fkc.referenced_column_id) /* * set condition as needed * ex. set pkt.name like '[pattern]' to search incoming constraints into pk table * ex. set fkcl.name like '[pattern]' to search the destination table */ where [condition] /* * set sort expression as needed */ ORDER BY [expression]

Regards,
Alberto
on April 14, 2010 at 9:07 pm | Reply Ricci

Another useful script for finding triggers and their related tables which I find useful:

SELECT tb.name, tr.name FROM sys.triggers tr
INNER JOIN sys.tables tb ON tr.parent_id = tb.object_id
on April 23, 2010 at 12:54 pm | Reply hshah

Hi pinal,

I am reviewing one database which has no foreign keys defined. I want to find relationship between the tables and one of the good thing is that they have used same columnnames in different tables if there is a relation between them. Like EmployeeID will remain as EmployeeID in all the tables.

Please help me to find unique relationship.
on April 23, 2010 at 1:32 pm | Reply hshah

I have used the following query. But this gives me the duplicates.I want to get unique result.

select o1.name as [Referencingtable] ,c1.name as [Referencingcolumn], o2.name as [Referencedtable] ,c2.name as [Referencedcolumn]
from sysobjects o1 join syscolumns c1 on o1.id = c1.id
join (sysobjects o2 join syscolumns c2 on o2.id = c2.id) on c1.[name] = c2.[name]
where o1.xtype = ‘u’ and o2.xtype = ‘u’ and o1.name != o2.name
on June 21, 2010 at 3:41 pm | Reply Kaushal

Really Cool site man……98% of my SQL server doubts are resolved in your site….

Pinal U Rule
on August 4, 2010 at 12:49 am | Reply Suyog

Hello Sir,

The query to get all Database level constraints is :

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE ‘%CONSTRAINT’

which u have already given……….

But my issue is
I am having 100 databases so for that i have to run the above query in all databases one by one…….
i tried sp_msforeachdb but not working…….

You have any query which will give me the above details for all Datbases in one shot…
- on August 4, 2010 at 1:48 pm | Reply Madhivanan
  
  Try this code
  
  sp_msforeachdb ‘
  SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
  SCHEMA_NAME(schema_id) AS SchemaName,
  OBJECT_NAME(parent_object_id) AS TableName,
  type_desc AS ConstraintType
  FROM ?.sys.objects
  WHERE type_desc LIKE ”%CONSTRAINT”’
- on August 4, 2010 at 1:49 pm | Reply Madhivanan
  
  If you want to include database name too, use this
  
  sp_msforeachdb ‘
  SELECT ”?”,OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
  SCHEMA_NAME(schema_id) AS SchemaName,
  OBJECT_NAME(parent_object_id) AS TableName,
  type_desc AS ConstraintType
  FROM ?.sys.objects
  WHERE type_desc LIKE ”%CONSTRAINT”’
on August 4, 2010 at 3:59 pm | Reply Ashish

if msforeachdb not working then try with cursor…
just giving you an example, which will go in each database and will print the database name using cursor. you can modify it as per your requirement :-

declare @name varchar(200)
declare c1 cursor for
select name from master..sysdatabases
open c1
FETCH NEXT FROM c1 into @name
WHILE @@FETCH_STATUS = 0
begin
print @name + ‘ is database name’
FETCH NEXT FROM c1 into @name
end
close c1
deallocate c1
on August 6, 2010 at 4:16 pm | Reply Ashish

Hi Pinal,
Generally I get any new comments addedd on this website to my mail id. But since last 2 days I am no more getting it.
Is there anything changed on this blog?
on August 26, 2010 at 1:22 pm | Reply مجتبی ولی

I think it is better when we change the condition

SELECT *, OBJECT_NAME(OBJECT_ID) AS ConstraintName,SCHEMA_NAME(schema_id) AS SchemaName,OBJECT_NAME(parent_object_id) AS TableName,type_desc AS ConstraintType FROM sys.objects WHERE (OBJECTPROPERTY(object_Id, N’IsConstraint’) = 1)

i don’t know if we gain a performance boost or not but it looks more reliable to me
on November 16, 2010 at 1:17 pm | Reply Raj

@abhay
Really helpfull….Thanks
on February 16, 2011 at 5:24 pm | Reply P V Nidheesh

While I was working in SQL server 2005, I used to know domain constraint have higher priority than referential constraint. Now I am little bit confusion regarding the priority among constraints in sql server. I will be thankful if u give a brief discussion regarding priority among constraints.
on March 25, 2011 at 12:22 pm | Reply Vishwas Khanal

Does any one have an idea about how to get the violated constrained name.

eg If I get an error Violation of PRIMARY KEY constraint ‘pk24′. Cannot insert duplicate key in object ‘xyz’.
I would like to catch pk24 alone.

Is there a way out?
- on April 1, 2011 at 12:03 pm | Reply madhivanan
  
  exec sp_pkeys xyz
  - on April 5, 2011 at 4:40 pm | Reply Vishwas Khanal
    
    madhivanan,
    
    ERROR_NUMBER() – catches error no,
    ERROR_MESSAGE() – catches error msg,
    ERROR_PROCEDURE() – catches procedure name ,
    ERROR_LINE() – catches line no
    
    How do i catch the constraint name if the error is about the violation of primary key?
    - on April 5, 2011 at 8:37 pm | Reply madhivanan
      
      You need to parse the error_message() and get that between contraint and cannot
on April 1, 2011 at 10:22 am | Reply rajendher

hi sir,
explain about constraints and uses of constraints
- on April 1, 2011 at 10:52 am | Reply madhivanan
  
  Read about it in SQL Server help file
on May 2, 2011 at 3:12 pm | Reply Ujwala

Sir,
The query of Default Constraint which u have given,i executed that but at d time of inserting d records in d table d is error has occured.
The default value which is hav to go automatically in the table that has not done.
Plz send me d feedback that how to do that.
Thanks.
on May 2, 2011 at 3:41 pm | Reply Anand M

hello sir i need ur help …
on May 2, 2011 at 3:42 pm | Reply Anand M

would u ple send me sql information
on May 19, 2011 at 6:42 pm | Reply Alderi Pereira

if object_id(‘tempdb..#tmp’) is not null
drop table #tmp

select
‘[' + SCHEMA_NAME(o1.schema_id) + '].[' + o1.name + ']‘ as Referencing_Object_name
, s.name as Constraint_name
, c1.name as referencing_column_Name
, ‘[' + SCHEMA_NAME(o2.schema_id) + '].[' + o2.name + ']‘ as Referenced_Object_name
, c2.name as Referenced_Column_Name
, fk.keyno as orderKey
, ‘[' + SCHEMA_NAME(o1.schema_id) + '].[' + s.name + ']‘ Constraint_name_schema
into #tmp
from sysforeignkeys fk
inner join sys.objects o1 on fk.fkeyid = o1.object_id
inner join sys.objects o2 on fk.rkeyid = o2.object_id
inner join sys.columns c1 on c1.object_id = o1.object_id and c1.column_id = fk.fkey
inner join sys.columns c2 on c2.object_id = o2.object_id and c2.column_id = fk.rkey
inner join sys.objects s on fk.constid = s.object_id
and o2.name=’<>’ — this predicate for a specific table
order by 1, fk.keyno

select distinct
‘IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N”’+ Constraint_name_schema + ”’) AND parent_object_id = OBJECT_ID(N”’ + Referencing_Object_name + ”’))’ + char(13) + char(10) + char(9) +
‘ALTER TABLE ‘ +
Referencing_Object_name +
‘ DROP CONSTRAINT ‘ +
Constraint_name
from #tmp o1

select distinct
‘ALTER TABLE ‘ +
Referencing_Object_name +
‘ WITH NOCHECK ADD CONSTRAINT ‘ +
Constraint_name +
‘ FOREIGN KEY ‘ +
‘(‘ +
STUFF(( SELECT
‘],[' + c1.referencing_column_Name
FROM #tmp c1
where c1.Referencing_Object_name = o1.Referencing_Object_name
group by c1.referencing_column_Name,orderKey
order by orderKey
FOR XML PATH('')
), 1, 2, '') + ']‘
+ ‘)’ + char(13) + char(10) + char(9) +
‘ REFERENCES ‘ +
Referenced_Object_name +
‘(‘ +
STUFF(( SELECT
‘],[' + c1.Referenced_Column_Name
FROM #tmp c1
where c1.Referenced_Object_name = o1.Referenced_Object_name
group by c1.Referenced_Column_Name,orderKey
order by orderKey
FOR XML PATH('')
), 1, 2, '') + ']‘
+ ‘)’
from #tmp o1
on June 9, 2011 at 1:15 am | Reply el jk

does anybody know how to list the column names which are included in the check constraint
- on June 10, 2011 at 2:02 pm | Reply madhivanan
  
  Look at these views
  
  select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
  select * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS
on July 6, 2011 at 4:35 pm | Reply Manoj

Hello,

Is there any way to get the data in one table is referencing in any other tables

For eg: I have 3 tables say tbl1,tbl2,tbl3.

tbl1 contains 3 records say 1,2,3

But only 1 and 2 are referencing tbl2 and tbl3,

If we delete the record 1 or 2 from tbl1 ,it is not possible.since the data is using in tbl2 or tbl3.

But if delete 3 from tbl1 it is possible.

So my question is, the way to find out whether the data is referencing in some other places without writing the delete query.

Through proc I pass the values 1,2,3 etc.

Thanks.
Manoj
- on July 7, 2011 at 7:06 pm | Reply madhivanan
  
  Use join or exists
  
  select * from table as t exists(select * from othertable ot where t.id=ot.id)
on July 12, 2011 at 10:18 am | Reply Manoj

I think it will not solve my problem.

The following query returns all related tables for tbl1.

select
o1.name as Referencing_Object_name
, c1.name as referencing_column_Name
, o2.name as Referenced_Object_name
, c2.name as Referenced_Column_Name
, s.name as Constraint_name
from sysforeignkeys fk
inner join sysobjects o1 on fk.fkeyid = o1.id
inner join sysobjects o2 on fk.rkeyid = o2.id
inner join syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkey
inner join syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkey
inner join sysobjects s on fk.constid = s.id
and o2.name=’tbl1′ and c2.name=’ID’

I also need to get is there any data in the referenced tables.

We can do this by writing a dynamic query by checking count in each table.

Other than this i am looking for a simplest way.

When we delete a record from the main table, and it has some references,we usually get the below error msg.

The DELETE statement conflicted with the REFERENCE constraint “FK_tbl3_tbl1″. The conflict occurred in database ” db1 “, table “dbo.tbl3″, column ‘ID’.
The statement has been terminated.

So definitely SQL Server should have some system procedures, that is checking this dependencies(means existence of records in related tables).

What is that sp..?

Thanks
on August 8, 2011 at 5:32 pm | Reply bkranthi

how to find out not null constraints columns in a table using sql command
on September 15, 2011 at 6:09 am | Reply Gustavo

Anyone know if there is any way to get the constaintas tha are defined as “with check add”?
on November 4, 2011 at 8:05 pm | Reply Pavana

Sir, if i ve given name to some constraints such as pk1 etc and at last if i want to know the constraint names that i ve assigned then hw can i see using select statements? Plz reply
- on December 5, 2011 at 10:26 pm | Reply Eric Russell
  
  A common scenario is where one needs write a schema change script to drop a column from a table, but first a constraint that references the column must be dropped. If the constraint has a name that was auto generated (ex: DF__MyTable_MyColumn__26E730D3), then that’s problematic, because the name may be different across dev, qa, prod, etc. So, here is an example for dynamically determining the name of constraint and then dropping it.
  
  declare @myconstraint varchar(8000);
  select @myconstraint = name
  from sys.default_constraints
  where object_name(parent_object_id) = ‘mytable’
  and col_name (parent_object_id, parent_column_id) = ‘mycolumn’;
  if @myconstraint is not null
  exec(‘alter table mytable drop constraint ‘+@myconstraint);
on November 15, 2011 at 4:35 pm | Reply Raja Jeevan Kumar Maduri

Folks,

I would want to run a query against a database where in I wanted to specify a column/field, which has been referenced in multiple DB tables. And the result of the query should retrieve me information about the field’s reference in a particular DB table and the column name being referenced along with the field description/documentation, if any. I am not a developer or a DBA. But, I just need this information for documentation. Thanks a ton before hand for responding back to my query.
on January 18, 2012 at 7:01 am | Reply SQL SERVER – Various Ways to Create Constraints – Quiz – Puzzle – 17 of 31 « SQL Server Journey with SQL Authority

[...] SQL Server Interview Questions and Answers ISBN: 1466405643 Page#137-139 Prevent Constraint to Allow NULL Create Default Constraint Over Table Column Create Primary Key with Specific Name when Creating Table Creating Primary Key, Foreign Key and Default Constraint How to ALTER CONSTRAINT Disable CHECK Constraint – Enable CHECK Constraint Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database Lis… [...]

Leave a Reply Cancel reply

Enter your comment here...

Fill in your details below or click an icon to log in:

Email (required) (Not published)

Name (required)

Website

You are commenting using your WordPress.com account. ( Log Out / Change )

You are commenting using your Twitter account. ( Log Out / Change )

You are commenting using your Facebook account. ( Log Out / Change )

Connecting to %s

free community tool
About Pinal Dave

Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 2000 articles on the subject on his blog at http://blog.sqlauthority.com. Along with 8+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is co-author of three SQL Server books - SQL Server Programming, SQL Wait Stats and SQL Server Interview Questions and Answers. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.

Follow @pinaldave Send +Pinal Dave an email at pinal@sqlauthority.com
Blog Stats
- 36,851,252 (36 Million+)
Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 16,067 other followers
Next Office Hours

Jan 3, 2012 1130 Indian Standard Time (0600 GMT)
Books I Authored

. Amazon|Flipkart|Kindle

. Amazon|Flipkart|Kindle

Amazon|Flipkart|Kindle
SQLAuthority Links

Subscribe to Newsletter
My Homepage
Windows Live Blog
--------------------
Top Downloads
PDF Downloads
Script Downloads

Script Bank
Favorite Scripts
All Scripts - 1
All Scripts - 2
All Scripts - 3

Top Articles
Best Articles
Favorite Articles - 1
Favorite Articles - 2
--------------------
> SQL Interview Q & A <
SQL Coding Standards
SQL FAQ Download
--------------------
Jobs @ SQLAuthority
About Nupur Dave

Nupur Dave loves technology simply because it makes life more convenient. She is devoted to technology because it touches our heart makes our daily lives easier. Among the many technological programs she uses and embraces Windows Live most because she can do lots of things with ease – from photo management to movies; business emails to personal social media connections.
Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet/ BOL. My employer do not endorse any tools, applications, books, or concepts mentioned on the blog. I have documented my personal experience on this blog.
- Home
- All Articles
- SQL Interview Q & A
- Blog Stats
- Contact
- Tools
  - Expressor
  - Pluralsight
  - Embarcadero
  - Manage Engine
  - Idera
  - Red Gate
  - SafePeak
- SQL Books
- >>Search<<
Categories
- About Me (145)
- Best Practices (143)
- Business Intelligence (36)
- CodeProject (10)
- Data Warehousing (49)
- Database (321)
- DBA (137)
- DMV (13)
- Joes 2 Pros (47)
- MVP (147)
- PASS (14)
- Readers Contribution (114)
- Readers Question (127)
- SharePoint (7)
- Software Development (69)
- SQL Add-On (99)
- SQL Azure (15)
- SQL Backup and Restore (79)
- SQL BOL (11)
- SQL Coding Standards (21)
- SQL Constraint and Keys (57)
- SQL Cursor (28)
- SQL Data Storage (59)
- SQL DateTime (47)
- SQL DMV (23)
- SQL Documentation (299)
- SQL Download (310)
- SQL Error Messages (161)
- SQL Function (161)
- SQL Humor (29)
- SQL in Sixty Seconds (1)
- SQL Index (155)
- SQL Interview Questions and Answers (139)
- SQL Joins (78)
- SQL Milestone (25)
- SQL Optimization (152)
- SQL PASS (20)
- SQL Performance (340)
- SQL Puzzle (96)
- SQL Security (127)
- SQL Server DBCC (42)
- SQL Server Management Studio (44)
- SQL Service Pack (13)
- SQL Stored Procedure (116)
- SQL String (26)
- SQL System Table (61)
- SQL Trigger (24)
- SQL User Group (57)
- SQL Utility (153)
- SQL View (26)
- SQL Wait Stats (41)
- SQL Wait Types (42)
- SQL White Papers (67)
- SQL XML (12)
- SQLAuthority (632)
  - SQL Training (19)
  - SQLAuthority Author Visit (141)
  - SQLAuthority Book Review (39)
  - SQLAuthority News (577)
  - SQLAuthority Website Review (42)
- SQLServer (228)
- Tech (1589)
  - Pinal Dave (1576)
  - SQL Scripts (862)
- Technology (2034)
  - PostADay (455)
  - SQL (2034)
  - SQL Authority (2034)
  - SQL Query (2033)
  - SQL Server (2034)
  - SQL Tips and Tricks (2034)
  - T SQL (2034)
- Video (5)
Top 3 Commenters

  2251- Madhivanan
  474 - Imran Mohammed
  301 - Ramdas Jaya

Follow

Follow “SQL Server Journey with SQL Authority”

Powered by WordPress.com