Journey to SQL Authority with Pinal Dave

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 | 30 Comments

30 Responses

1. on September 16, 2007 at 11:07 pm 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

   
   
2. on September 30, 2007 at 10:38 pm 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.

   
   
3. on October 2, 2007 at 6:03 pm pinaldave

   Hi Ujjaval Suthar,

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

   Regards,
   Pinal

   
   
4. on October 23, 2007 at 7:43 am Rob

   This query works and was very helpful to me. Thanks.

   
   
5. on December 21, 2007 at 1:11 am 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

   
   
6. on December 21, 2007 at 5:47 pm 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!!!!!

   
   
7. on December 21, 2007 at 6:11 pm Sandeep

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

   thx

   
   
8. on December 28, 2007 at 2:25 pm 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?

   
   
9. on January 18, 2008 at 7:21 pm Tom W

   1 column should be for continent.
   Put ‘Asia’ in as the value for all the Asian coutries, etc.

   
   
10. on February 22, 2008 at 3:20 pm 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

    
    
11. on March 27, 2008 at 10:44 pm 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

    
    
12. on March 29, 2008 at 7:42 am 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

    
    
13. on April 16, 2008 at 4:34 pm 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!!!!

    
    
14. on May 5, 2008 at 2:57 pm Henko

    Thanks for the script above, it really helped me on deleting my DWH constraints. Cheers.

    
    
15. on May 12, 2008 at 5:07 pm 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!

    
    
16. on May 24, 2008 at 6:16 pm 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.

    
    
17. on June 23, 2008 at 2:41 pm Tim4it

    szolarp & Tushar

    Many thanks .. you really helped me ..

    
    
18. on August 4, 2008 at 5:00 pm 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!!!!!

    
    
19. on August 7, 2008 at 4:47 pm Ankush

    What r the differences b/w Sql Server 2005 and 2000

    
    
20. on August 26, 2008 at 10:04 am 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

    
    
21. on August 29, 2008 at 10:27 pm 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

    
    
22. on August 30, 2008 at 3:44 am 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.

    
    
23. on September 12, 2008 at 10:51 pm 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…

    
    
24. on September 27, 2008 at 4:17 am 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

    
    
25. on October 31, 2008 at 5:34 pm suresh

    i was seaching for the query regarding constraints. It was nice and thankx

    
    
26. on February 5, 2009 at 8:25 am Sajid

    Superb site…

    This site really provides simple solutions for complex problems

    Thanks alot

    
    
27. on February 24, 2009 at 5:40 pm 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… [...]

    
    
28. on May 19, 2009 at 3:43 pm Rama

    hey pinal

    i get this error when i run ur script

    ‘SCHEMA_NAME’ is not a recognized function name.

    ne thoughts?

    
    
29. on May 22, 2009 at 12:02 am Shihab

    Is there way to find DEFAULT_CONSTRANTS of the table from INFORMATION_SCHEMA ?

    
    
30. on May 22, 2009 at 11:21 am 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.