Update multiple tables with same column name and view relationships
Greetings,
I inherited DB admin role cuz our DB admin left. I ran the following query to find all the tables in a DB that have the same column name.
USE AdventureWorks
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name;
I have two questions.
1. How can I find out how the tables are related to each other (foreign key, primary key, etc.).
2. How can I update (if I need to) all the tables from the above query.
Thank you.
I inherited DB admin role cuz our DB admin left. I ran the following query to find all the tables in a DB that have the same column name.
USE AdventureWorks
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name;
I have two questions.
1. How can I find out how the tables are related to each other (foreign key, primary key, etc.).
2. How can I update (if I need to) all the tables from the above query.
Thank you.
sventhan
in SSMS, you can right click on the table and select dependencies.
you could use
DECLARE @TableName SYSNAME
SET @TableName = '[dbo].[employee]' <==== yourtable name
SELECT 'Parent tables' AS TableConnectionType,
OBJECT_SCHEMA_NAME(referen
OBJECT_NAME(referenced_obj
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID(@TableName)
UNION
SELECT 'Child tables' AS TableConnectionType,
OBJECT_SCHEMA_NAME(parent_
OBJECT_NAME(parent_object_
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID(@TableName)
DECLARE @TableName SYSNAME
SET @TableName = '[dbo].[employee]' <==== yourtable name
SELECT 'Parent tables' AS TableConnectionType,
OBJECT_SCHEMA_NAME(referen
OBJECT_NAME(referenced_obj
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID(@TableName)
UNION
SELECT 'Child tables' AS TableConnectionType,
OBJECT_SCHEMA_NAME(parent_
OBJECT_NAME(parent_object_
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID(@TableName)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sharath_123, Thank you very much for your response. I'm getting incorrect syntax near AS line 10. Is there a typo there?
>> I'm getting incorrect syntax near AS line 10
There is no syntactical error in the query.Did you copy-paste the query as provided and run?
There is no syntactical error in the query.Did you copy-paste the query as provided and run?