June 27, 2013 at 7:48 am
I must be doing this wrong..becouse I get columns with no matching table.
Here is the query I created to show tables that have a column with the name "solutionscenariocode" as the column name.
USE [asccdb]
select col.[name] as Column_Name, tbl.[name] as Table_Name
from sys.columns col
inner join sys.tables tbl
on tbl.[object_id] = col.[object_id]
where col.[name] like 'solutionscenariocode'
Checking query should be empty, but it is not
USE [asccdb]
select col.[name] as Column_Name, tbl.[name] as Table_Name
from sys.columns col
left outer join sys.tables tbl
on tbl.[object_id] = col.[object_id]
where col.[name] like 'solutionscenariocode' and tbl.[object_id] is NULL
June 27, 2013 at 7:59 am
From a quick test I got similar results, but then using sys.objects I found the the objects in the second query were all views not tables as you were checking for in the first query.
I would suggest you modify the queries to:-
SELECT col.[name] AS Column_Name
, tbl.[name] AS Table_Name
FROM
sys.columns col
INNER JOIN sys.tables tbl
ON tbl.[object_id] = col.[object_id]
INNER JOIN sys.objects obj
ON col.object_id = obj.object_id
WHERE
col.name LIKE '%solutionscenariocode%'
AND obj.type_desc = 'USER_TABLE'
SELECT col.[name] AS Column_Name
, tbl.[name] AS Table_Name
, col.*
FROM
sys.columns col
INNER JOIN sys.objects obj
ON col.object_id = obj.object_id
LEFT OUTER JOIN sys.tables tbl
ON tbl.[object_id] = col.[object_id]
WHERE
col.[name] LIKE '%solutionscenariocode%'
AND tbl.[object_id] IS NULL
AND obj.type_desc = 'USER_TABLE'
Don't forget to use % for wildcards in like too. 😉
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
June 27, 2013 at 8:21 am
Thanks... forgot about views. I do not want a wildcard.. since I need to find all tables with that exact name in it... so I can create a SQL to generate the statement to clean out bad data.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply