How To Find All Primary And Foreign Key Columns In All Database Tables

Apr 17, 2014

how to find all primary key columns & foreign key columns in all database tables?

View 1 Replies


ADVERTISEMENT

Creating Indexes On Columns That Are Foreign Keys To Primary Keys Of Other Tables

Jul 16, 2014

what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:

[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------

The foreign key above is as:

ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])

What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:

CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)

In other words, what's best practice for adding an index which best supports a Foreign Key constraint?

View 4 Replies View Related

T-SQL (SS2K8) :: Find Primary Key Table Name And Field Name Of Foreign Key

Apr 10, 2015

How can i find the primary field name and primary table name of a given foreign key.

For example

Table A:
IDa
Column1
Column2
Column3

Table B:
IDb
column1
column2
column3 <---- this is foreign key to table A's IDa

What i want to do is i pass input of tableB, column3 and i get name of Primary tableA and field name IDa. How is it possible to do in tsql ?

View 4 Replies View Related

Find Out The Columns Should Be Referred Through Foreign Key

Aug 2, 2007

For example if we clearly define a foreign key in the master and child table as following script -

CREATE TABLE master(pkey int PRIMARY KEY, data varchar(10))
GO
CREATE TABLE child (fkey int CONSTRAINT fk_master_child
FOREIGN KEY (fkey) REFERENCES master(pkey))


We can find out the two tables reference relationship by looking at INFORMATION_SCHEMA tables.

However, if the two tables are created in this way €“


CREATE TABLE master(pkey int PRIMARY KEY, data varchar(10))
GO
CREATE TABLE child (fkey int)


Does any one know how to programming verify the actual reference relationship exists between pkey and fkey in these two tables?


Thanks

View 1 Replies View Related

Can Only Use Primary Key And Not Foreign Key While Joining Tables?

Oct 9, 2015

I was trying a joining example provided in my book in which customer is a table and person is another table. The query provided in the book is this... USE AdventureWorks2012;

GO
SELECT c.CustomerID, c.PersonID, p.BusinessEntityID, p.LastName
FROM Sales.Customer AS c
INNER JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID;

This is the query that I did....

SELECT
      c.CustomerID,p.FirstName,p.MiddleName,p.LastName
FROM
      Sales.Customer AS c INNER JOIN Person.Person AS p
ON
      c.CustomerID=p.BusinessEntityID

ORDER BY
         p.BusinessEntityID;

Keys :-

Person Table
[PK_Person_BusinessEntityID]
[FK_Person_BusinessEntity_BusinessEntityID]

Customer Table
[PK_Customer_CustomerID]
[FK_Customer_Person_PersonID]

However,both of them gives a very different result set.But my question is why do we need to use the Customer.PersonID instead of Customer.CustomerID. Is it really important to use one primary key and one foreign,is there any specific reason why the book showed c.personId=p.BusinessEntityId.??

View 3 Replies View Related

Updating Tables In Sequence With Primary Key And Foreign Key Relations

Feb 7, 2007

Hi all,
       In my project i will have the  data in a collection of objects, I need to update series of tables with foreign key relations
       Right now my code looks like this
       foreach(object obj in Objects){
       int accountId=Account.Insert(obj.accountOpenDate,obj.accountName);//this will update the accounts table and returns account id which is a Identity column in Acccounts table
       int DebtId=Debt.Insert(accountd,obj.debtamount,obj.debtbalance); this will update the Debts table and returns DebtId
       ///series of tables like above but all the relevant data comes from obj and in the Insert Methods i am using stored procedures to Insert the data into table
       }
      The no of objects varies from 1000 to 1 milliion,, with this approach its taking more time to update all the data. Please let me know if any alternative procedure to handle this kind of scenario.
 
Thanks
Regards
Prasad.

View 2 Replies View Related

SQL - Foreign Key With References Of Multiple Tables With Same Primary Key Field

Apr 9, 2007

I want to create a table withmember id(primary key for Students,faculty and staff [Tables])and now i want to create issues[Tables] with foreign key as member idbut in references i could not able to pass on reference as orcondition for students, faculty and staff.Thank You,Chirag

View 3 Replies View Related

SQL 2012 :: Insert Data Onto Tables Having Primary And Foreign Key Relations?

Oct 31, 2015

Is there anyway to get the order in which data to be import on to tables when they have primary and Foreign Key relations?

For ex:We have around 170 tables and when tries to insert data it will throw error stating table25 data should be inserted first when we insert data in table 25 it say 70 like that.

View 3 Replies View Related

Reporting Services :: How To Create 2 Tables With Primary / Foreign Key Relationship

Jun 6, 2015

I want to create a table with primary key , and put relationship with second table.

View 5 Replies View Related

Query Foreign Key Columns And Tables

Dec 20, 2006

I am trying to query the database to get me the foreign key columns and the tables they belong to.I have: The name of the tableI need:The name of the column in the target tableThe name of the column in the referenced tableThe name of the referenced table  Any help would be great, thanks 

View 5 Replies View Related

Import Csv Data To Dbo.Tables Via CREATE TABLE &&amp; BUKL INSERT:How To Designate The Primary-Foreign Keys &&amp; Set Up Relationship?

Jan 28, 2008

Hi all,

I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:

-- ImportCSVprojects.sql --

USE ChemDatabase

GO

CREATE TABLE Projects

(

ProjectID int,

ProjectName nvarchar(25),

LabName nvarchar(25)

);

BULK INSERT dbo.Projects

FROM 'c:myfileProjects.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO
=======================================
-- ImportCSVsamples.sql --

USE ChemDatabase

GO

CREATE TABLE Samples

(

SampleID int,

SampleName nvarchar(25),

Matrix nvarchar(25),

SampleType nvarchar(25),

ChemGroup nvarchar(25),

ProjectID int

);

BULK INSERT dbo.Samples

FROM 'c:myfileSamples.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO
=========================================
-- ImportCSVtestResult.sql --

USE ChemDatabase

GO

CREATE TABLE TestResults

(

AnalyteID int,

AnalyteName nvarchar(25),

Result decimal(9,3),

UnitForConc nvarchar(25),

SampleID int

);

BULK INSERT dbo.TestResults

FROM 'c:myfileLabTests.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO

========================================
The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.

2 questions to ask:
(1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables?
Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period?
(2) How can I set up the relationships among these 3 dbo Tables?

Please help and advise.

Thanks in advance,
Scott Chang

View 6 Replies View Related

Script: Find Tables Without Primary Keys

Aug 3, 2002

...got tired of looking at them by hand.

Cheers
-b

DECLARE @vcDB varchar(20),@vcSchema varchar(20),@vcTable varchar(200)

Select @vcDB='mydb',@vcSchema='dbo'

DECLARE cLoop cursor for
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_CATALOG=@vcDB
and TABLE_SCHEMA=@vcSchema
order by TABLE_NAME ASC

open cLoop

FETCH NEXT FROM cLoop INTO @vcTable
WHILE @@FETCH_STATUS=0
BEGIN
if not exists (SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = @vcSchema
AND TABLE_NAME = @vcTable
AND CONSTRAINT_TYPE = 'PRIMARY KEY')
print @vcTable + ' does not have a primary key'

FETCH NEXT FROM cLoop INTO @vcTable
END
Close cLoop
DEALLOCATE cLoop

View 2 Replies View Related

How To Find All Tables That Have An Identity Column As A Primary Key

Mar 6, 2008

How do i find all the tables that have an Identity Column as a primary key in a database. Thanks.

View 8 Replies View Related

Columns In Primary Table And Foreign Key Table

Feb 12, 2007

mahesh writes "HI,

I am new to sql server.

can anybody help me

I have a table named tblqualificationmaster.

can i know the foreignkeys and the table related to this

tblqualificationmaster having foeign keys using stored procedure."

View 1 Replies View Related

How To Find Missing Records From Tables Involving Composite Primary Keys

Nov 23, 2006

Table 1







     Code
    Quarter

500002
26

500002
27

500002
28

500002
28.5

500002
29

 

Table 2







     Code
           Qtr

500002
26

500002
27

 

I have these two identical tables with the columns CODE & Qtr being COMPOSITE PRIMARY KEYS

Can anybody help me with how to compare the two tables to find the records not present in Table 2

That is i need this result







    Code
   Quarter

500002
28

500002
28.5

500002
29

I have come up with this solution

select scrip_cd,Qtr,scrip_cd+Qtr from Table1 where
scrip_cd+Qtr not in (select scrip_cd+qtr as 'con' from Table2)

i need to know if there is some other way of doing the same

Thanks in Advance

Jacx

View 3 Replies View Related

SQL Server 2012 :: Find Queries That Lock Tables Or Not Using Primary Key While Running Update

Jul 20, 2015

I need to search for such SPs in my database in which the queries for update a table contains where clause which uses non primary key while updating rows in table.

If employee table have empId as primary key and an Update query is using empName in where clause to update employee record then such SP should be listed. so there would be hundreds of tables with their primary key and thousands of SPs in a database. How can I find them where the "where" clause is using some other column than its primary key.

If there is any other hint or query to identify such queries that lock tables, I only found the above few queries that are not using primary key in where clause.

View 2 Replies View Related

Enter Default Values For All Columns In All Tables Except Primary Keys

Jul 23, 2005

How can i enter Default Values of " " to all the columns of type characterof all the tables (excluding system tables) and Default Values of 0of all columns of type numbers. Excluding all primary key columns.Thank you

View 1 Replies View Related

How To Find Out Missing Columns Between Tables

Apr 21, 2015

We are trying to find out the difference between tables in CUSTOMER database and CUSTOMER_coded database. The goal is to find out if there are any columns missing in each table of CUSTOMER_coded database.

We need the list of tables in CUSTOMER_coded database that misses some column compare to its peer in CUSTOMER database (list of columns being missing also).

I googled, but I get only all the columns in tables of database.

I need missing columns of all the tables when we compare these 2 databases( CUSTOMER and CUSTOMER_coded  databases).

View 8 Replies View Related

Truncate Database Tables Based On Foreign Key Constraints

Nov 5, 2007

Guys,

I have 600 tables in my database, out of which 40 tables are look up value tables. I want generate truncate scripts which truncates all the tables in order of Parent child relationship excluding lookup tables. Is there any way to do this apart from figuring out Parent Child relationship and then writing the truncate statements for each of the table.

For example

EmployeeDetail table references Employee table
DepartmentDetail table references Department table
Department table references Employee table

My truncate script should be

TRUNCATE TABLE DEPARTMENTDETAIL
TRUNCATE TABLE EMPLOYEEDETAIL
TRUNCATE TABLE DEPARTMENT
TRUNCATE TABLE EMPLOYEE

Is there any automated way to figure out parent and child tables and generate truncate script for the same.

Thanks

View 1 Replies View Related

Transact SQL :: Find Unmatching Columns Between Two Tables While Applying A Change Column Prefix Table

Nov 16, 2015

For our ETL process, we maintain a TransformationList table that has the source view and the destination table. Data is copied from the view into the table (INSERT INTO). I am trying to find column names in the Views that are not column names in the associated Table.

In the below example, want to end up with three records:

 1, View1, Column4
 2, View2, Column4
 2, View2, Column5

I have it almost working, except that there is a table, ChangeColPrefix table, that is used by the ETL process to change some of the view's column name prefixes. Some of the source views have column names with prefixes that do not match the destination table column names. Say view SouthBase has all the column names prefixed with SB - like SBAcct, SBName. And the Destination table of Area District has ADAcct, ADName. There would be a row in the ChangeColPrefix for SouthBase, SB, AD, 1, 2 that would be used by the ETL process to create the INSERT INTO Area District From SouthBase.

I need to use this ChangeColPreifx to find my unmatching columns between my source views and destination tables. With out that table SBAcct and SBName from SouthBase will not appear to match the columns of ADAcct and ADName, but they do match.

I want to end up with these three records as non-matching:

View1, Column4
View2, Column4
View2, Column5

View1 has Salumn2 and View2 has Salumn5, and they must be changed to Column2 and Column5 as per the ChangeColPrefix table before running the Select from INFORMATION_SCHEMA.COLUMNS EXCEPT Select from INFORMATION_SCHEMA.COLUMNS looking for unmatched columns.

/***** Set Up Test Data *****/
-- Create 2 test views
IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]'))
DROP VIEW dbo.[View1]
GO
CREATE VIEW View1
AS SELECT '1' AS Column1 , '2' AS Salumn2 , '4' AS Column4;

[Code] ....

View 3 Replies View Related

How To Find All The Tables Which Are In Database

Feb 24, 2008

 Hi......i want to find all the tables which are in database. is there any hint or code that can help meThanks in advance

View 4 Replies View Related

Foreign And Primary Key Question

Apr 24, 2007

OK - I have a two tables in a database. Table one contains an ID, 'oneID', field as the primary key. It is auto-incremented. Table two has ID field, 'twoID', as the primary key. This field also auto-increments. Table two also has 'oneID' as the foreign key.
Now, my question is, how do I get the foreign key in table two to auto-increment in conjuction with table one's primary key? They are after all the same data. Do I have to manually code to get table one data and save it to table two data?
thanks

View 1 Replies View Related

Clustered Primary Key And Foreign Key: T-SQL

Jun 10, 2005

I am having a little trouble getting this to work right, but have come a ways since I started this.......other tables created first and with no problems..... then these two with the last table being the problemI need to set one foreign key in the second table referencing the first table.But, the primary key is clustered with the two foreign keys and I get the error....There are no primary or candidate keys in the referenced table 'courseScores' that match the referencing column list in the foreign key 'FK_course'.CREATE TABLE dbo.courseScores   (     courseId varchar(20) NOT NULL    CONSTRAINT FK_courseId_courseStructure2 FOREIGN KEY (courseId)    REFERENCES courseStructure (courseId), 
    studentId varchar(20) NOT NULL    CONSTRAINT FK_studentId_students2 FOREIGN KEY (studentId)    REFERENCES students (studentId), 
    CONSTRAINT PK_courseScore PRIMARY KEY CLUSTERED (courseId, studentId)
          )CREATE TABLE dbo.objScores  (  tmp int IDENTITY(1,1) PRIMARY KEY,    objective varchar(50) NOT NULL,        courseId varchar(20) NOT NULL    CONSTRAINT FK_course FOREIGN KEY (courseId)    REFERENCES courseScores  (courseId)                  )
Once I get it working, then the tmp will be gone and then set 3 foreign keys as the clustered primary, fyi.Not sure how to reference half a primary key?Any help is greatly appreciated.....Thanks all,Zath

View 4 Replies View Related

Can A Column Act As Both Primary Key And Foreign Key?

Sep 11, 2011

Can a column act as both primary key and foreign key, if so what is script.

And can a table have more than one primary keys/foreign keys, if so what is the script.

View 4 Replies View Related

Foreign Key Value Has No Matching Primary Key

Sep 15, 2014

I'm currently using Oracle and receiving this error:

SQL Error: ORA-02291: integrity constraint (43437001.ATHLETE_FK1) violated - parent key not found
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause: A foreign key value has no matching primary key value.
*Action: Delete the foreign key or add a matching primary key.

When inserting this value:

INSERT INTO ATHLETE VALUES ('A001','TOM','HANKS','03-FEB-90','USA','C001');

With Tables:

CREATE TABLE ATHLETE(
ATHLETEID CHAR(4),
ATHLETEFIRSTNAME VARCHAR2(20),
ATHLETELASTNAME VARCHAR(20),
ATHLETEDOB DATE,
REPCOUNTRY VARCHAR2(12),

[Code] ......

I think the error is to do with the Athletes table (primary/foreign key)

How would I fix this in order to insert values without error?

View 3 Replies View Related

Foreign And Primary Keys

Oct 19, 2005

I have an application in which i need to get the foreign key fieldsfrom a table and then get all the foreign keys primary key field fromthe linking table. Could some one tell me how i do this usingINFORMATION_SCHEMA. I have tried and can get the foreign keys but notsure how to get the associated primary keys.

View 1 Replies View Related

Composite Primary Key And Foreign Key

Feb 8, 2008



Hi,
In my table1, I have a composite primary key ssn+firstname+lastname.
In table2 I have ssn, firstname, lastname, col1. In table2 I want to create a foreign key which references table1 primary key?
table1 and table2 has the primary, foreign key relationships. Still table2 need primary key. In table2 Can I make the ssn+Firstname+Lastname as primary key? or one column id as auto increment?

Which is best way?

Thanks

View 27 Replies View Related

Primary Key/foreign Key Relationship

May 11, 2007

I'm using MS SQL Server Studio Express 9.0. When selecting SQL Server Compact Edition, I'm able to create tables and primary keys with no problem. But how do I create a relationship with another table (foreign key)?

Bob K.

View 11 Replies View Related

How To Find The Last Updated Tables In A Database

May 28, 2007

Hi,



I am trying to create a data dictionary for a huge application which has aroung 300 tables in the database....when i perform any operation in the application some tables are updated.... can you help me to find out how can we find out the last updated tables in the database ??

View 1 Replies View Related

To Find Out Empty Tables In A Database

Jul 20, 2006

Hi all,

I have a database which gets its daily feed from a ftp file. Now is there any way i can figure out the empty tables in the databese which doesnot get any data on the feed.

Thanks in advance,

View 10 Replies View Related

Getting Information Dependant Upon Primary && Foreign Key

Aug 11, 2006

Hi All,
It seems I have been requested to carry out a complex query and the best way I think I can do this is with the use of a stored procedure. The problem is that I am not quite sure whether my SP is stated correctly and also how I would go about stating the SP in my VB.net code!
 
I would be ever so grateful if somebody could look over my SP code and possibly recommend a way of stating my code. My ability is limited so I would appreciate it if examples could be used with possible relations to my problem.
 
The Problem?
 
Tables:
 
1.tblRisk: Ref(pk), subject, status(fk), staff(fk), Dept(fk)
 
2.tblDept: Ref(pk), Department
 
The SP should state that Department should appear as the end result of the query when the page is loaded. So when a row is selected in tblRisk, dependant upon what the Dept is in that table, it then populates the department in which it is associated with from tblDept. I have left the SP below.  
 
Many Thanks,
Kunal   
 
CREATE PROCEDURE dbo.ShowMe @yourInputValue INTAS  SELECT tblDept.Department    FROM tblDept    JOIN tblRisk      ON tblDept.Ref = tblRisk.Dept  WHERE tblDept.Ref = @yourInputValue RETURN 0GO

View 1 Replies View Related

Foreign Key Constraint To A Composite Primary Key

Mar 6, 2008

I have two tables: table 1 and table 2. The primary key of table is composite key of two collumns and table 2 is the child of parent table 1. Is it possible to create / define a foreign key constraint using a composite primary key?

View 10 Replies View Related

Delete Data Which Is A Primary And Foreign Key

May 26, 2008

Hi,
How do I delete data which is a Foreign Key in another table?
For example;
string query = "DELETE * from user_details WHERE user_ID = '" +userID.Text+ "';
The user_ID is the Primary Key in the user_details table and also a Foreign Key in other tables.
Thank you. (:

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved