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)
Community Initiatives
About Pinal Dave
Pinal Dave is a Pluralsight Developer Evangelist. He has authored 9 SQL Server database books and have written over 2500 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 9+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Prior to joining Microsoft he was awarded the Microsoft MVP award for three continuous years for his contribution in the community. Here is the list of the Pinal Dave's books.
Follow @pinaldave
Send +Pinal Dave an email at pinal@sqlauthority.com-
- 63,278,268 (63 Million+)
SQL in Sixty Seconds
SQL Books
Funny Index Video
SQLAuthority Links
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
Top 3 Commenters
-
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
Its really helpful infromation.
Lots of hidden trissures are found in all articales of this web
RELALLY GRT ONE
Itz good…..
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.
Hi Ujjaval Suthar,
Yes, I am Pinal from Gandhinagar. Nirma 1999-2003.
Regards,
Pinal
This query works and was very helpful to me. Thanks.
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
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!!!!!
sir,
i got my answer, initially i didn’t search in sqlauthority.com!!
thx
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?
1 column should be for continent.
Put ‘Asia’ in as the value for all the Asian coutries, etc.
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
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
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
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!!!!
Thanks for the script above, it really helped me on deleting my DWH constraints. Cheers.
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!
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.
szolarp & Tushar
Many thanks .. you really helped me ..
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!!!!!
What r the differences b/w Sql Server 2005 and 2000
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
it is so useful…… :)
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
@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
[...] SQL SERVER – 2005 – List All The Constraint of Database – Find Primary Key and Foreign Key Constrain… [...]
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
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
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
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…
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”’
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”’
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
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?
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
@abhay
Really helpfull….Thanks
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.
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?
exec sp_pkeys xyz
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?
You need to parse the error_message() and get that between contraint and cannot
hi sir,
explain about constraints and uses of constraints
Read about it in SQL Server help file
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.
hello sir i need ur help …
would u ple send me sql information
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
does anybody know how to list the column names which are included in the check constraint
Look at these views
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
select * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS
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
Use join or exists
select * from table as t exists(select * from othertable ot where t.id=ot.id)
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
how to find out not null constraints columns in a table using sql command
Anyone know if there is any way to get the constaintas tha are defined as “with check add”?
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
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);
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.
[...] 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… [...]
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
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
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
hi
can be apply constraint like default,check
on views
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
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.
Thank you, exactly what I needed as usual. Love your blog and keep up the handy SQL tips.
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
Hi,
I am a beginner any one please tell me what is different between schema and Default schema
this doesn’t work in SQL 2005
‘SCHEMA_NAME’ is not a recognized function name.