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 (https://blog.sqlauthority.com)
SQL SERVER – 2005 – List All The Constraint of Database – Find Primary Key and Foreign Key Constraint in Database
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
101 Comments. Leave new
@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.
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…
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
i was seaching for the query regarding constraints. It was nice and thankx
Superb site…
This site really provides simple solutions for complex problems
Thanks alot
hey pinal
i get this error when i run ur script
‘SCHEMA_NAME’ is not a recognized function name.
ne thoughts?
Is there way to find DEFAULT_CONSTRANTS of the table from INFORMATION_SCHEMA ?
@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.
hi,
im a beginner..please tell me syntax how to create a table within a schema?..
@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.
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.
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.
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
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.
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
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.
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
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
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.
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
Really Cool site man……98% of my SQL server doubts are resolved in your site….
Pinal U Rule