Find a specific column entry in an unknown table in a database?

sql sql-server sql-server-2008-r2 information-schema
Hal picture Hal · Apr 28, 2011 · Viewed 29k times · Source

I'm aware of this topic ( Find a specific column in an unknown table in a database? ) and my problem is quite similar. The query I need is quite similar to this one (I think):

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE '%watcher%'

But what I need is a query where the column name is unknown, but I know what the content will be and I want to find out what the name of table/column is. (I know this sounds strange :-/ ). I this possible?

Answer

Timothy Walden picture Timothy Walden · Apr 29, 2013

Try using ApexSQL Search – it searches for both objects and data and it’s a free tool similar to SQL Search from Red Gate.

Another option if you don’t want to deal with third party tools is query that will use cursors to iterate through all columns in all tables but I’m afraid that it would turn out too complex and performance intensive.

Related questions

Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build). Sometimes when I run my build I get this error: ALTER DATABASE failed because a lock could not be placed on …

Read More
Select SQL Server database size

how can i query my sql server to only get the size of database? I used this : use "MY_DB" exec sp_spaceused I got this : database_name database_size unallocated space My_DB 17899.13 MB 5309.39 MB It returns me several …

Read More
CREATE TABLE IF NOT EXISTS equivalent in SQL Server

CREATE TABLE IF NOT EXISTS works on mysql but fails with SQL Server 2008 R2. What is the equivalent syntax?

Read More