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
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
101 Comments. Leave new
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.
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.
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..