January 9, 2020 at 5:06 pm
Is there another way of getting a list of tables that all contain the same 3 column names without using a CTE or subquery?
;WITH a AS(
SELECT DISTINCT TableName = t.name, ColumnName = c.name
FROM sys.tables t
JOIN sys.columns c ON t.object_id=c.object_id
WHERE c.name IN ('Column1','Column2','Column3')
)
SELECT
a.TableName
FROM a
GROUP BY a.TableName
HAVING COUNT(a.ColumnName)>2
ORDER BY a.TableName
January 9, 2020 at 5:20 pm
Sure,
SELECT DISTINCT COL_ONE.TABLE_NAME, COL_ONE.TABLE_SCHEMA FROM INFORMATION_SCHEMA.COLUMNS COL_ONE
INNER JOIN INFORMATION_SCHEMA.COLUMNS COL_TWO ON COL_ONE.TABLE_NAME = COL_TWO.TABLE_NAME AND COL_ONE.TABLE_SCHEMA = COL_TWO.TABLE_SCHEMA
INNER JOIN INFORMATION_SCHEMA.COLUMNS COL_THREE ON COL_ONE.TABLE_NAME = COL_THREE.TABLE_NAME AND COL_ONE.TABLE_SCHEMA = COL_THREE.TABLE_SCHEMA
WHERE
COL_ONE.COLUMN_NAME = '<your column>'
AND COL_TWO.COLUMN_NAME = '<your column>'
AND COL_THREE.COLUMN_NAME = '<your column>'
January 9, 2020 at 5:32 pm
SELECT OBJECT_NAME(object_id)
,object_id
FROM sys.columns
WHERE name IN ('Column1','Column2','Column3')
GROUP BY OBJECT_NAME(object_id)
,object_id
HAVING COUNT(1) > 2;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 9, 2020 at 5:33 pm
SELECT OBJECT_NAME(c.object_id) AS TableName
FROM sys.columns c
INNER JOIN sys.tables t ON t.object_id = c.object_id
WHERE c.name IN ('Column1','Column2','Column3')
GROUP BY c.object_id
HAVING COUNT(*) = 3
ORDER BY TableName
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 9, 2020 at 6:34 pm
Thank you all !!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply