Select every row from every table in database where (columnName = value) if column name exists

I'm using SQL Server 2005 and am logged in as sa. I would like to query every table in my database for whether it has a certain column name. And if so append every row where (columnNameValue = someValue) to a results table. Then return said results table.

There are a few similar problems with solutions out there. Notably I can use sp_MSForeachTable for this but it lacks any kind of documentation. I can use SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES to get a list of all the tables.

The following solution (link text) will go every table & every column to find a certain value in the cell. Which is different from finding a certain value in the cell IF that column is a certain columnName.

Writing a nested while statement should be possible but are there any inbuilt commands to run queries like this?

Pseudo-Code if it helps :

foreach(table in tableList) {
    if (table.hasColumnName(SOME_COLUMN) {
       EXEC ('SELECT * FROM table WHERE (SOME_COLUMN = SOME_VALUE)')
    }
}

[Edit]

Rather then having a single results set I would like to be a single result per table as long as the select returns at least one row. This should give a very large amount of different results as expecting a join or union to work is unrealistic.

If possible I would like to append the tablename to start of each result.

I have a simple query below that gets all the results but It will display empty tables and doesnt give any visual indication in individual results as to what table it belongs to:

[Further Edit]

Updated the query below to include the IF EXIST check that removes NULL results & the select name AS source column to add the table source to the results thanks to @Martin

DECLARE @COLUMN_VALUE nvarchar(512), @VALUE nvarchar(10);

SET @COLUMN_VALUE = 'id'
SET @VALUE = '0';

DECLARE @TABLE_NAME nvarchar(512), @COLUMN_NAME nvarchar(512), @QUERY nvarchar(512);

SET @TABLE_NAME = '';

WHILE @TABLE_NAME IS NOT NULL
BEGIN
    SET @TABLE_NAME =
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM INFORMATION_SCHEMA.TABLES
        WHERE QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TABLE_NAME
    );
    PRINT 'Table name : ' + @TABLE_NAME;
    SET @COLUMN_NAME = 
    (
        SELECT MIN(QUOTENAME(COLUMN_NAME))
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE (TABLE_NAME = PARSENAME(@TABLE_NAME, 1))
            AND (COLUMN_NAME = @COLUMN_VALUE)

    );
    PRINT 'Column name : ' + @COLUMN_NAME;
    IF @COLUMN_NAME IS NOT NULL 
    BEGIN
        SET @QUERY = 
            'SELECT ''' + @TABLE_NAME + ''' AS Source, * ' +
            'FROM ' + @TABLE_NAME + ' ' +
            'WHERE (' + @COLUMN_NAME + ' = ' + @VALUE + ')'
        EXEC
        (
            'IF EXISTS(' + @QUERY + ') ' + @QUERY
        )
    END     
END

Answers


CREATE TABLE foo
(
SOME_COLUMN VARCHAR(10)
)

CREATE TABLE bar
(
SOME_COLUMN VARCHAR(10)
)

INSERT INTO bar VALUES ('SOME_VALUE')


DECLARE @Query nvarchar(max)

SELECT 
      @Query = isnull(@Query + ';','') + 
      'IF EXISTS(SELECT * FROM ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + 
      QUOTENAME(o.name) + ' WHERE SOME_COLUMN=''SOME_VALUE'')
      SELECT ''' + o.name +''' AS Source, * FROM ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + 
      QUOTENAME(o.name) + ' WHERE SOME_COLUMN=''SOME_VALUE'''
FROM sys.columns c 
JOIN sys.objects o
ON o.object_id = c.object_id
WHERE o.type IN ('U','V') AND c.name = 'SOME_COLUMN'

EXEC sp_executesql @Query

Need Your Help

Deploying Golang web app static files with Docker container

deployment go docker static-files

I'm working on a small web application that has some static files (configs and html templates):

What is the best Rails way to implement a continuous sidebar search?

ruby-on-rails ajax search

I am trying to make a recipe website. I would love to have a sidebar where the user can add one ingredient at a time, where the main body would refresh and show only recipes that have that ingredie...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.