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
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.
how can check all constraint in sql?
I do not want constrains applied to tables in a database.
I want constraint list which i can apply to a table.
hello pinal sir ,
when we checked our database name from webpanel
one of the table in the database automatically changes its schema object or schema name i.e. from dbo to “username” and corresponding table gets truncated
What is the context?
sir u mean to say “DBcontext”
I didn’t understand the question.
Are you saying that table definition is getting changed automatically? You need to run profiler and find out who is doing it.
hello sir,
actually we are having database present on local sql server having schema name as “dbo” as we made a script to restore that db on remote server say with username “saheb” .
since in websitepanel we use to have one “users” section which provide username and password to db so
our problem is when we tried to use database with their credentials in sql server ..then only “single table” schema name get changed from “dbo.tablename” to that of username “saheb.tablename”
thanks for your assistance
i want to share one snap which makes you understand what exactly we are talking about
I am still not clear. you can post on https://www.facebook.com/groups/sqlBangalore/ and someone can help you quicker.
i think you are try to saying as when you try to move database on remote server and use it ,its schema change from dbo to another and get invalid object name error ,,,,,is it right???
:)
ok , sir i will explain you the fact
i am a .net developer with 2.10 exp
so, i have one database with name say “xyz” running properly on local sql server 2012 with windows authentication. now, there is one table whose name is “salesperday” with schema object “.dbo” this table gets updated continuously on one of the “windows running service”
so my query is that
when i restore the “.bak ” file of that “xyz” database on remote server with authentication as “sqlserver” with its credentials it works fine
but after some time as the service goes on and on the schema object of the “salesperday” changes from “dbo” to username say “username.salesperday.” now ,this is username one which we provide to authenticate the database .i am providing you the query
here also clustered index and several related keys all get vanished and table gets truncated or vanished completely .
USE [xyz]
GO
/****** Object: Table [dbo].[SalesPerDay]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SalesPerDay](
[ID] [uniqueidentifier] NULL,
[Date] [datetime] NOT NULL,
[AAccountID] [uniqueidentifier] NOT NULL,
[ASIN] [char](10) NOT NULL,
[QtySold] [decimal](18, 9) NULL,
[SalesAmount] [decimal](18, 9) NULL,
CONSTRAINT [PK_SalesPerDay] PRIMARY KEY NONCLUSTERED
(
[AAccountID] ASC,
[ASIN] ASC,
[Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE UNIQUE CLUSTERED INDEX [SalesPerDay_ID] ON [dbo].[SalesPerDay]
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Is the default schema getting changed for that user? I would suggest you to post on facebook group https://www.facebook.com/groups/SQLBangalore and someone else can give more ideas.
thanks sir for your precious time
we solve that problem
actually there was one store procedure which was droping that table and recreating it and while doing so its taking the schema obj as that of username ,
so, what we did we had define “dbo.” as schema obj of that table and it works..
thank you..
thnx a lot… its very helpful for me..
Abhijit – Thanks for letting me know.
Display Names of persons having Address in all cities where a specified person is living in sql server 2008R2