CODEWITHSUNDEEP
sqldatabase-schema
ScrumMaster Guy
ScrumMaster Guy

Reputation: 267

Search column name having specific value in tables in certain database

Is there any way I can search a column having specific value I am trying to find through all tables in a database? For example I have 

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 '%VersionID%'
ORDER BY schema_name, table_name;

Which I found from searching. It gives me the table names that "versionID" column exists, but how can I search to return table names that for example have a value of "35" for that column.

Thanks in advance,

I apologize, maybe I am not being clear in my request. Maybe this cannot be done in SQL because I have not found anything through my research. But let me clarify.

Running this script 

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 '%factor%'
--and table_name in (Select name from sysobjects where ratingVersionID = 35)
ORDER BY schema_name, table_name;

Will return this for example: Query Results

But let say, the "factor" column in "AutoAge_Factor" table does NOT HAVE any records matching to "35". How can I eliminate that table from returning in the results. But I would really prefer the format of the results be this way because I would like to use this return as a loop and do some other stuff within the loop.

Thanks again!

Upvotes: 3

Views: 12647

Answers (3)

isakavis
isakavis

Reputation: 785

declare @tableName varchar(100), @colName varchar(100) declare @sqlStatement nvarchar(100)

declare tablelist cursor for select s.name, c.name from sys.columns c inner join sys.tables s on s.object_id = c.object_id where c.name like'%YourSearchCondtion%' OPEN tablelist FETCH NEXT FROM tablelist into @tableName, @colName

while @@FETCH_STATUS = 0 BEGIN

SELECT @sqlStatement = 'SELECT ' + @colName + ', * FROM ' + @tableName + ' WHERE ' + @colName + ' NOT LIKE ''%35%'''

exec sp_executesql @sqlStatement

-- Here you can get the table that you dont want and add to a temp table.. PRINT CAST(@@ROWCOUNT AS VARCHAR)

FETCH NEXT FROM tablelist INTO @tableName, @colName END

CLOSE tablelist DEALLOCATE tablelist GO

Upvotes: 0

isakavis
isakavis

Reputation: 785

select s.name, c.name from sys.columns c inner join sys.tables s on s.object_id = c.object_id where c.name like'%35%'

This will give you the columns with 35 and the associated table.

Upvotes: 0

podiluska
podiluska

Reputation: 51514

This modification to your sql will generate more SQL that you can run 

SELECT 
    'select '''+SCHEMA_NAME(schema_id) +'.'+t.name + ''', '+c.name + ' from ' 
      + SCHEMA_NAME(schema_id) +'.' + t.name
      + ' where ' + c.name + ' = 35'

FROM sys.tables AS t 
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID 
WHERE c.name LIKE '%ID%' 
ORDER BY schema_name(schema_id), t.name

You could make the SQL run automatically if necessary, but the syntax for that depends on your platform. Alternatively, copy the results into your query tool and run them, if that suffices

Upvotes: 2

Related Questions