Skip to main content

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

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.

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'

We can also use following query:

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

Following query is also useful to see all Paren-Child key Relationship with table Names & Column Names:


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

Alternatively we can also use System Views & Tables to get specific information.

Comments

Post a Comment

Popular posts from this blog

How to Prevent Users to Press F5 key (Refresh button)

Some times we want to prevent user to Press F5 key to prevent Page Refresh. You can use the below script. Paste this code in the Head Section of your Page. <script language=javascript> document.onkeydown = function(){ if(window.event && window.event .keyCode == 116){ window.event.keyCode = 505; // Capture and remap F5 } if(window.event && window.event .keyCode == 505){ // New action for F5 return false; // Must return false }} </script>
7 comments
Read more

Access Different interface method with same Name implemented in a class

We use interface and multiple interface inherit it into class, then we implement the methods of interface as it is necessary but what will happen if two interface have the same mentods with same signature and inherited in the same class? Now questions arises from here are : Q1) Both the methods will be implementable which is with the same name and signature? Q2) If methods are implementable then how it is possible? Q3) If it is possible then can we access both the methods? Q3) If it is possible then what is the way to access these methods publically? The answer of all the above question is below: 1) Yes both the methods with same name and signature is implementable. 2) we have to implement interface explicitely preceding with iterface name. 3) Yes it is possible to access the methods. 4) Yes we can
Post a Comment
Read more

Should have on tips of our lips.........ACID PROPERTIES

Introduction    When ever we process any DML statements transactions are implicitly called. Ie. when ever no begin  transaction statement insert ,delete, update statements will have transaction statements around them implicitly. ACID concept is one of the oldest and important properties of the Database. It sets forward four goals that every database management system must strive to achieve: atomicity, consistency, isolation and durability. No database that fails to meet any of these four goals can be considered reliable. Atomicity Atomicity states that database modifications must follow an "all or nothing" rule. Each transaction is said to be "atomic." If one part of the transaction fails, the entire transaction fails. It is critical that the database management system maintain the atomic nature of transactions in spite of any DBMS, operating system or hardware failure. Consistency Consistency states that only valid data will be written to the database. If,...
2 comments
Read more