CODEWITHSUNDEEP
sqldatabasesql-server-2008
Brijesh Patel
Brijesh Patel

Reputation: 2958

How to find in all the tables containing columns having particular values?

My aim is to find all the table from database having the given column with particular value. i.e. I have tables like Table1,Table2,Table3,Table4,Table5,Table6, Table7 etc.

In these tables, some of the tables have column name 'NameID'. Now I want to find out the tables which have column name 'NameID' and value is 100.

Can any one tell me how to write SQL Query for that?

Upvotes: 4

Views: 5030

Answers (4)

regeter
regeter

Reputation: 1552

You can use my search script with the following parameters:

SET @SearchStrColumnValue = '100' /* use LIKE syntax */
SET @FullRowResult = 1
SET @SearchStrTableName = 'TABLE%' /* NULL for all tables, uses LIKE syntax */
SET @SearchStrColumnName = 'NameID' /* NULL for all columns, uses LIKE syntax */
SET @SearchStrInXML = 0 /* Searching XML data may be slow */

http://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns/

Upvotes: 0

Greg
Greg

Reputation: 3522

Needed to do something similar, for us it was TransactionID. I took Paul Alan Taylor's and MakeMinePanacea suggestion and added a cursor. It's quick and dirty but works for a 1 off.

DECLARE @TableName varchar(MAX)
DECLARE @Sql VARCHAR(MAX)
set @Sql = ''
DECLARE @Tables CURSOR

SET @Tables = CURSOR FOR

    SELECT t.Table_Name
    FROM INFORMATION_SCHEMA.COLUMNS c
    JOIN INFORMATION_SCHEMA.TABLES t on c.Table_Name = t.Table_Name
    WHERE COLUMN_NAME = 'TransactionID' and table_type = 'BASE TABLE'

OPEN @Tables
FETCH NEXT
FROM @Tables INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    if len(@Sql) > 0
    BEGIN
        set @SQL = @SQL + ' UNION ALL '
    END

    set @Sql = @Sql + 'SELECT ''' + @TableName + ''' FROM [' + @TableName + '] WHERE transactionid = 100'

    FETCH NEXT
    FROM @Tables INTO @TableName
END
CLOSE @Tables
DEALLOCATE @Tables

exec (@Sql)

Upvotes: 0

Levin
Levin

Reputation: 2005

If you can't list the tables, I doubt you can do it in a query. You'll need to dynamically create a query (using the tables you found like Paul Alan Taylor showed).

Try adapting this:

http://ubercode.bleakllc.com/2008/11/find-any-value-in-any-database-column.html

If you already know all the tables that have the column, and that list of tables isn't subject to change, then you can do a simple query like 

  Select 'Table1' from table1 where id = 100
  UNION
  Select 'Table2' from table2 where id = 100
  UNION
  ...

UNION will throw out all the dupes so if multiple rows have id 100, you only get the table listed once, if that's not what you want UNION ALL.

Upvotes: 0

Paul Alan Taylor
Paul Alan Taylor

Reputation: 10680

Yep, you can use INFORMATION_SCHEMA, which allows MS SQL to self-describe.

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'NameID'

Upvotes: 0

Related Questions