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.
Last Comment
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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?
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
