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.
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)

About these ads

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

  1. 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

    Like

  2. 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.

    Like

  3. 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

    Like

  4. 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!!!!!

    Like

  5. 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?

    Like

  6. 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

    Like

  7. 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

    Like

  8. 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

    Like

  9. 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!!!!

    Like

  10. 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!

    Like

  11. 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.

    Like

  12. 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!!!!!

    Like

  13. 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

    Like

  14. 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

    Like

  15. @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.

    Like

  16. 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…

    Like

  17. 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

    Like

  18. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  19. @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.

    Like

  20. @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.

    Like

  21. 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.

    Like

  22. 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.

    Like

    • 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

      Like

  23. 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.

    Like

  24. 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

    Like

  25. 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.

    Like

  26. 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

    Like

  27. 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

    Like

  28. 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.

    Like

  29. 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

    Like

  30. 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…

    Like

    • 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”’

      Like

    • 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”’

      Like

  31. 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

    Like

  32. 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?

    Like

  33. 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

    Like

  34. 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.

    Like

  35. 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?

    Like

  36. 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.

    Like

  37. 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

    Like

  38. 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

    Like

  39. 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

    Like

  40. 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

    Like

    • 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);

      Like

  41. 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.

    Like

  42. Pingback: SQL SERVER – Various Ways to Create Constraints – Quiz – Puzzle – 17 of 31 « SQL Server Journey with SQL Authority

  43. I am new to sql…given this job in our company because another person quit.
    weeeeeee LOL

    SQL SERVER 2008 R2
    anyway I have to merge 2 databases (finally got it done) one has more columns and tables than the other so I had a heck of a time.

    Now they have a 3rd production database they want to remove all data and put the merged data into it.
    So I get a copy of the production one and remove all data

    MAKE SURE to PRESERVE THE SCHEMA so we an just take this one and put it into production.

    HUH???

    anyway I remove all data and start importing. Tough to put a real into an int.
    Ok I change the int into a real on import of the table

    later its datetimes into short dates ETC this is a nightmare
    then I look and all the primary keys and foreign keys (IN view) no longer show they are linked.

    ENOUGH whining, my question.

    I have the original PRODUCTION DATABASE and I am working in a copy.

    HOW do I get the pk and fk information from the original database and put it in the copy to restore all the keys and links like the production one?

    one person I talked to said just backup script on the original.
    ok then what…oh I don’t know just put it in the copy.
    then what
    NO MORE IDEAS I am lost

    Like

  44. Hi,

    I am doing database upgradation work from 2005 to 2008, for which we are just taking backup of 2005 and restoring to 2008. I am preparing some generic scripts which will identify that amount columns of all tables have exactly same value in both 2005 and 2008, and all ther non-amount columns also have same value row wise for each table.

    Could someone please help if there is any generic script which can be used to reconcile data between both version of databases table wise>row wise .

    Please help.

    Dev

    Like

  45. Hi,

    COuld someone please help me on my below query :

    I want to know primary column field names at run time for supplied table name. there are around 500 tables which i am using at runtime and want to know table wise primary key column names at runtime.

    Please help!1

    Like

  46. Hi Pinal,

    Can you please help me to get the constraint information from a view in sql server.I need to know the list of columns,pf,fk.

    I used the following query but it only gives me information from tables but not from a particular view.But it returns NULL for CONSTRAINT_TYPE column.

    Please suggest.

    Thanks in Advance.
    -Shovan

    select INFORMATION_SCHEMA.COLUMNS.TABLE_NAME ,
    INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME ,
    INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE ,
    INFORMATION_SCHEMA.COLUMNS.DATA_TYPE ,
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
    from INFORMATION_SCHEMA.COLUMNS left outer join
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE on INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
    and INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
    and INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_CATALOG = INFORMATION_SCHEMA.COLUMNS.TABLE_CATALOG
    and INFORMATION_SCHEMA.KEY_COLUMN_USAGE.ORDINAL_POSITION = INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    on INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME and
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
    where INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = ‘your view name here’
    order by INFORMATION_schema.COLUMNS.TABLE_NAME

    Like

  47. You are a SQL king. The thing I’m finding for days, i got here. Not only it solve my problem but the problems that I’m thinking i will face in future all are available in your single script. Thanks a lot for such a nice work.

    Like

  48. Hi There ! I am working with system Auto generated table constraint names. I’m confused what the constraint name which has prefix as XPK followed by table name. for example my table has a constraint name XPKApplicationStatusHistory …. ? It will be awesome if someone explains what does that prefix means. Thank you

    Like

  49. Pingback: SQL SERVER – Weekly Series – Memory Lane – #047 | Journey to SQL Authority with Pinal Dave

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s