Find All Tables That A Table Is Related To
Sep 8, 2006how can i find all the names of tables that a specific table is related to in tsql?
View 3 Replies
ADVERTISEMENT
Table Name Convention For Related Tables
Nov 1, 2007Hi,
If I have 2 tables:
TableA
aID
aCount
TableB
bID
bCount
And I need to create a 3rd table that will look like:
aID
bID
What should I call this table?
I've seen:
relAB
AXB
Any other naming conventions?
One Table From Two Related Tables: Any Transformation Avaiable?
Dec 4, 2007
Hi everyone!
I'm on my way to learn SSIS by myself and it's a little complicated!
I'd like to ask you one thing:
I have two tables at my data source, one is "Clients" and the other one is ClientsAddress. That is, a client can have more than one address. Both tables are related by a one to many relationship and the tables description is:
CLIENTS CLIENTSADDRESS
#PK_Client #PK_Client
other fields.. #ID_address
... other fields
What i intend to do is to obtain one table with approximately 3 fields, each one for a possible client address; something like this:
CLIENTS
PK_Client
ID_Address1
ID_Address2
ID_Address3
My question is what transformation can i use? an how ?
Thanks very much in advance!!
Emilio Leyes
Salta, Argentina
Newbie-DELETE A Record In A Table A That Is Related To Table B, And Table B Related To Table A
Mar 20, 2008Hi thanks for looking at my question
Using sqlServer management studio 2005
My Tables are something like this:
--Table 1 "Employee"
CREATE TABLE [MyCompany].[Employee](
[EmployeeGID] [int] IDENTITY(1,1) NOT NULL,
[BranchFID] [int] NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[MiddleName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeGID]
)
GO
ALTER TABLE [MyCompany].[Employee]
WITH CHECK ADD CONSTRAINT [FK_Employee_BranchFID]
FOREIGN KEY([BranchFID])
REFERENCES [myCompany].[Branch] ([BranchGID])
GO
ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]
-- Table 2 "Branch"
CREATE TABLE [Mycompany].[Branch](
[BranchGID] [int] IDENTITY(1,1) NOT NULL,
[BranchName] [varchar](50) NOT NULL,
[City] [varchar](50) NOT NULL,
[ManagerFID] [int] NOT NULL,
CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED
(
[BranchGID]
)
GO
ALTER TABLE [MyCompany].[Branch]
WITH CHECK ADD CONSTRAINT [FK_Branch_ManagerFID]
FOREIGN KEY([ManagerFID])
REFERENCES [MyCompany].[Employee] ([EmployeeGID])
GO
ALTER TABLE [MyCompany].[Branch]
CHECK CONSTRAINT [FK_Branch_ManagerFID]
--Foreign IDs = FID
--generated IDs = GID
Then I try a simple single row DELETE
DELETE FROM MyCompany.Employee
WHERE EmployeeGID= 39
Well this might look like a very basic error:
I get this Error after trying to delete something from Table €œEmployee€?
The DELETE statement conflicted with the
REFERENCE constraint "FK_Branch_ManagerFID".
The conflict occurred in database "MyDatabase",
table "myCompany.Branch", column 'ManagerFID'.
Yes what I€™ve been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a €œBranch€? entry, basically each entry in €œbranch€? and €œEmployee€? is child of each other which makes things more complicated.
My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use €œON DELETE CASCADE€? or something?
Thanks
T-SQL (SS2K8) :: Find All Related IDs?
Aug 13, 2015I have a Transactional table containing IDs that relate to each other. The table highlights the transactions that move one ID to another, by storing an ID and a IDNew (Basically from ID to ID).
My requirement is to create a Inline Table Valued Function that will efficiently return a single ID column to show all related IDs by looking at the ID and the IDNew fields.
I need to pass in a single ID to get all relations or a list of IDs through xml.
I have managed to achieve this by making use of two recursive cte's and then combining the results with union statements to create a distinct list of values.
IF OBJECT_ID (N'tempdb..#Trans', N'U') IS NOT NULL
BEGIN
DROP TABLE #Trans
END
CREATE TABLE #Trans
(
TranID int identity(1,1),
[code]....
What Is The Best And Most Effective Way To Find The Most Related Phrases?
Dec 10, 2007Hi, I am looking for a sql statemnt that find the most related phrases, how to do that? For example, I want to find the related phrases bellow for the following phrase: "How to install the software"
1- "Installation does not work"
2- "installation"
3- "steps to install the software"
It's obvoius that phrase number 2 and 3 are related to that phrase, how to do that using sql?
Regards.
SQL 2012 :: Possible To Find All Tables That Has A Join With Given Table In Database
Jan 7, 2015I need to find all tables which has a join (either inside an sp, view, etc) with my given table in a db.
sys.dm_sql_referencing_entities doesn't work here.
Note: i dont want to identify by FK References, for 2 reasons:
1) tables might not met with a join (just FK was defined)
2) sometimes, a join happened between tables, without an FK defined
SQL Server 2014 :: Find Count For Two Tables And Put Into Other Table
Jun 18, 2015I have two table in two different database. I have to found count for both table and put into other table.
example : Database --> A , table is T1
Database --> B , table is T2
I want count of both table and put into T3 table in database A or B .
How To Find All Child Tables That Belong To A Parent Table
Mar 5, 2008How do i find the child tables that belong to a parent table. Thanks.
How Can I Find The Matching Table From A Group Of Tables? (difficult To Explain...)
Aug 9, 2007Let's say I have a list of IDs called EntryID and each EntryID can belong to ONE table out of a group of six, what is the best way to get a listing of these?
For example:
select r.*
from #Reminders r
left join mytable1 mt1 on (r.EntryID = mt1.EntryID)
left join mytable2 mt2 on (r.EntryID = mt2.EntryID)
left join mytable3 mt3 on (r.EntryID = mt3.EntryID)
left join mytable4 mt4 on (r.EntryID = mt4.EntryID)
As you can see, #Reminders has one field called EntryID (and many rows).
In my example above, only ONE of those tables will actually be able to join but I have no idea which one has the matching EntryID.
What is the best way for me to do this? I want to grab "ReportStatus" from the corresponding "mytable"... (each "mytable" has a ReportStatus column)
Need To Find An Easy Way To Split A Column In Table Without Using Cursor Or Temp Tables
Sep 5, 2007Hi ,
I have two tables within a SQL database. The 1st table has an identified column and column which lists one of more email identifers for a second table,
e.g.
ID Email
-- ----------
1 AS1 AS11
2 AS2 AS3 AS4 AS5
3 AS6 AS7
The second table has a column which has an email identifier and another column which lists one email address for that particular identifier, e.g.
ID EmailAddress
--- ------------------
AS1 abcstu@emc.com
AS2 abcstu2@emc.com
AS3 abcstu3@emc.com
AS4 abcstu4@em.com
AS5 abcstu5@emc.com
AS6 abcstu6@emc.com
AS7 abcstu7@emc.com
AS11 abcstu8@emc.com
I need to create a stored procedure or function that:
1. Selects an Email from the first table, based on a valid ID,
2. Splits the Email field of the first table (using the space separator) so that there is an array of Emails and then,
3. Selects the relevant EmailAddress value from the second table, based on a valid Email stored in the array
Is there any way that this can be done directly within SQL Server using a stored procedure/function without having to use cursors?
Many Thanks,
probetatester@yahoo.com
Transact SQL :: Find Unmatching Columns Between Two Tables While Applying A Change Column Prefix Table
Nov 16, 2015For 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] ....
Related Tables
Jul 23, 2005Is it possible to retrieve all tables that a given one is related tovia foreign keys?
View 2 Replies View RelatedInserting Into Related Tables
Mar 24, 2004Hi all,
I am trying to insert user's input from a web form into the tables. For example,
PURCHASE TABLE(PurchaseID, PurchaseNo, Date, PartID)
PART TABLE(PartID, PartDescription,MachineID)
MACHINE TABLE(MachineID, MachineName)
On the web form I have textboxes for the Purchase No., date and part description, and a drop down list for the machine name. How do I insert them into the different tables?
I've just start learning ASP.NET and I am using Web Matrix for this. The examples I've seen so far only shows how to insert into a single table.
Thanks!
Setting Up FTS On Related Tables
Sep 12, 2005I have 2 related tables I want to include in a single full text search. How would I go about setting this up?
View 1 Replies View RelatedSelect 2 Related Tables
May 25, 2006hi this is probably a dumb question with an easy answer but does anyone know how to do this...
2 tables, one is a supplier list (id, supplier_name) and the other is an order list (id, order_name, supplier_id... etc)
basically i need to join the 2 tables together (id=supplier_id) but i need to display the results without the supplier_id and the supplier table id, only the supplier name?
do i have select only the specific fields I want? or can I exclude the supplier_id and the id from the supplier table?
Joining Not Related Tables
Mar 6, 2007Hi all. how could i join unrelated tables?
for example:
table1: employee
dcno name address telno
1 sarah philippines 111
2 john india 222
3 joy usa 333
table2: hospital
hospname location owner
hosp1 loc1 billgates
hosp2 loc2 ann
hosp3 loc3 love
hosp4 loc4 vic
table3: student
studname load
kim 10
Is it possible to join unrelated tables?
Thanks
-Ron-
Indexes On Related Tables...
Jun 17, 2007Hi,
I have 2 tables with this design: one has ArticleID as primary key and multiple other fields and one has GroupID as primary key and multiple other fields. Each article can belong in multiple groups so I created a new table called articleGroups with only 2 fields: ArticleID and GroupID to show the groups associated with each article. There is a relation between this table and each of the main 2 tables. My question is, in the articleGroup table, does it make any sense to create an Index on ArticleID, GroupID or both? Since the group is needed for each article the Groups will always be queried everytime the article is queried. So, I am not sure if an index is needed?
Thank you,
Regards,
Iulian
Select All From Two Many-to-many Related Tables
Oct 11, 2007Hello, everyone!
Please give your ideas about how to create a query from two tables, which have many-to-many relationship. For example, we have table Book and table Author and consider, that one author had wroten several books and one book had being wroten by several authors. So we have many-to-many relationship of these two tables.
Usually this sutuation resolves by maens of creating the third table AuthorBook, which consist of key fields from the Author table and key fields from the Book table. So that the Author table is the parent table to AuthorBook, and the Book table is the parent table to AuthorBook. So we have two relations one-to-many. Now the question is how to get all related values from both tables, using JOIN operation and the conjunctive table?
I would greatly appreciate any answers
INSERING DATA INTO RELATED TABLES
Nov 14, 2007I have a relationship database that contains two related tables. Table1 has the fields Customer_Name, Customer_ID, and products_Purchased. Table2 has the fields Customer_Name and Cutomer_ID. I would like to add a new row to Table1for a new purchase by a cutomer. When I try to add a new value in Table1 I get an error message saying I can't add a value to table1 because it requires a corresponding value in Table2. I am using the following code: '**********************************************
("INSERT INTO [Table1]([products_Purchased], [Customer_ID]) VALUES('" & textBox..Text & "',"
& session("ID") & ")"
' *****************************************
How do I write an INSERT STATEMENT that adds vaules to both tables, so that the code works?
Any HELP would be appreciated.
charlie
Retrieve Data From 3 Tables Which Are Related To Each Other
May 28, 2007Hey,
I want retrieve data from 3 table my tables structure is this
tblUsers
U_ID - Name
3 John
tblGroups
G_ID - Name
5 Admins
6 Moderators
Now I want join some of the users to different groups for example John maby is a member of two groups (Admins and Moderators)
in order to do this I created a new table names tblGroupsUsers
tblGroupsUsers
ID - User_ID - Group_ID
1 3 5
1 3 6
its ok, but Now I don't know how to retrive my users list from database I don't know how to write a wuery for this
I have tried this :
strSQL = "SELECT tblUsers.name, tblUsers.U_ID, tblGroups.G_ID, tblGroupsUsers.Group_ID, tblGroupsUsers.User_ID FROM tblUsers INNER JOIN tblGroupsUsers ON tblGroupsUsers.User_ID = tblUsers.U_ID, tblGroups WHERE tblGroupsUsers.Group_ID = tblGroups.G_ID ORDER BY tblUsers.name ASC;"
Its working withut error but the problem is the results its like this
John
John
its will retrive the username twice , I think its reading based on tblGroupsUsers table because it has two rows ,
help please I need this how can I configure my query to get eache name once
Thanks
Insert Data Into Related Tables
Mar 2, 2008i am using ms sql server 2005 express for the following:
T1 = {t1.c1(PK), t1.c2, t2.c1(FK)}
T2 = {t2.c1(PK), t2.c2}
where T1 and T2 are tables from the same database, and tn.cn are CoulmnN of TableN
T1 and T2 are linked by the relationship t2.c1
i need to know how to add records to T1 and T2 using ms sql.
record for T2 needs to be added first then retreive t2.c1 from T2 to add record to T1 with t1.c1, t1.c2 t2.c1
please help, thanks
Import Excel Into 2 Related Tables
Jan 29, 2014I have 1 Excel spreadsheet. It has about 50 fields. I need to import this into my Database.The problem is; there are 2 tables that I need to import this into. These 2 tables are related with [ID].So, I need to split the data from 1 spreadsheet between 2 SQL server table. and keep the records related.
View 4 Replies View RelatedDelete Records In Two Related Tables?
Jan 30, 2014I am trying to delete the records in 2 related tables. The 'child' table has a field called [SETA],
I want to delete all the records in this table that contain the same info, as well all the fields in the parent table that is related to this table. They share the [ID] field as key. This is my code:
Code:
DELETE FROM Student a full outer JOIN Qualification b on a.[ID] =b.[ID] WHERE b.[SETA] = @SETA
How To Insert Records Into Related Tables?
Sep 24, 2007Hi,
I have 3 tables:
Employees with the fields:idEmployee and employeeName
Roles with the fields:idRole and roleName.
An employee can have one or many roles.
I created the table EmployeeRoles with the fields: id,idEmployee,idRole.
idEmployee and idRole are foreign keys.
I want to insert a new employee into Employees table, but I have to insert idEmployee field into EmployeeRoles table.
How can I do this?
Thanks
Retrieve ALL Related Tables Through TSQL
Apr 30, 2008Many HUGE Thanks to the person who knows how to do this.
Problem: My goal is to take a single data row (myDataRow) from some table (MyTable) and then retrieve every single associated record throughout the database that is related to that datarow. This requires me to first find out what tables are related to the starting table and then recursively pull all of the associated records until I get all of the records associated. Sounds simple right?
The following code only pulls back only those tables that have a child table
with an associated foreign key (i.e. a 1 -> many):
Select [Name] from sysobjects where xtype='U' and Id in(
select fkeyid from sysforeignkeys where rkeyid=
(Select Id from sysobjects where Name='<TableName>'))
order by name
But this query does not work backwards where I start with a child table
and want to know the parents (i.e. many->1 relationship)
Anybody have a clue how to retrieve these parent related tables when starting with a child table? I know it can be done because SQL Server Management lets you select a table (child OR parent) and retrieve the related table(s).
Thank you for any direction you can suggest!
Best Wishes to All
-Eric
Anything That You Find In SQL Object Scripts, You Can Also Find Them In System Tables?
Jul 26, 2005I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
View 4 Replies View RelatedLoading Data In SQL Server To Related Tables (how)
Jul 14, 2004Hi I have a question how to load data to tables linked by Foreign Keys in MSDE/SQL server. Example:
If I have 2 tables linked (by Foreign Key):
One table:
ITEM idITEM NAMEITEM CATEGORY (FK)
1cheese 2
And another:
Category IDCATEGORY NAME
1 household
2 food
3 general
How do I enter the load of data
Do I have to enter it as
1cheese2
or is there some way of entering it as
1cheesefood
TDS wizard does not allow me to transfer to views/querries what I thought would be a normal way as I would enter data to view(relevant to Access's form) and it would update related tables . When I wrote sql to do it it said I can not update my view table as too many tables would be affected(I had lookup tables empty then though)
I am doing it by number using TDS wizard to transfer it directly to the main table but there must be a better way
Insert Stored Procedure For Related Tables
Feb 24, 2005I have two sets of related tables: Quote - QuoteDetail and Order - OrderItem
I need to copy Quote - QuoteDetail records to Order - OrderItem tables
I have the stored procedure up to this point: Insert a Quote in the Order table and get the new Order @@Identity.
I need to insert the QuoteDetail records into the OrderItem table using the new OrderID
Thank you for your help.
INSERTING MULTIPLE RELATED TABLES IN A TRANSACTION
Jun 2, 2005I have a problem there must be an answer to, but I cannot find it anywhere on Google.I have a SQL INSERT statement in an ASP.Net page that inserts into 3 tables in one transaction.The problem is that 2 of the 3 tables are children of the main table, and I need to get the Parent table's Primary Key [which of course has not been inserted yet] to insert into the Child tables.How do I do this?THanks.Doug
View 1 Replies View RelatedHow To Get Data From All Related Tables Using Stored Procedure
Sep 16, 2005I have a situation where I want to load some entities from one table lets say the table is customers and i would like to load all the customers with first name = dummy, not only this i would like to load all the orders and order details for these specific customers (these are two different separate tables) . I want all this within one stored procedure that return me three results for three different tables. Please tell me whether it is possible and how.
View 1 Replies View RelatedSQL 2012 :: DVM And System Tables Related To DB Mirroring
Jul 14, 2014Need to know some of the DMV'S and System Tables related to Database Mirroring.
View 2 Replies View RelatedInserting Data Into Multiple Related Tables
Jun 18, 2007Hey guys up until now i've only inserted data into a single table. Now I have a form that collects information over a span of three forms. Each form has a table related to it and these three tables are related to each other.
What I want to know is:
1)How do you go abouts inserting data into multiple related tables that have constraints on them?
2)Would you use a stored procedure in an instance like this?
3)At what stage would you execute the sql queries. I assume you do this once you have collected all the required information as opposed to: Enter info into form1, submit form1 data to database... enter info into form2, submit form2 data into database etc
Any help would be greatly appreciated!
Say for instance I have three related tables.
table1
------
tbl1_id
tbl1_data1
tbl1_data2
table2
------
tbl2_id
tbl2_data1
tbl2_data2
table3
------
tbl3_id
tbl3_data1
tbl3_data2
table1 has a one-to-many relationship with table2
table3 has a one-to-one relationship with table2