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;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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) A socialist is someone who will give you the shirt off *someone else's* back.
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