Select data from same structure table

Question

I have a table format like this :

CODE    DESIGNER    BRAND   GENDER  SIZE    UNIT    TYPE    TOT
M1001      JOE       DIDI     M      1.9     oz      HH      88 

There are over one thousand tables in the same format. Each Row has a unique CODE. This CODE will be present in every table. I want grab the rows with this unique CODE in this tables. Output are suppose to be the following:

CODE    DESIGNER    BRAND   GENDER  SIZE    UNIT    TYPE    TOT
M1001      JOE       DIDI     M      1.9     oz      HH      98 
M1001      JOE       DIDI     M      1.9     oz      HH      88 
M1001      JOE       DIDI     M      1.9     oz      HH      78 
M1001      JOE       DIDI     M      1.9     oz      HH      48 

My software is SQL sever 2014.

Answer 1

Temp table with dynamic sql will by far be the easiest if looking at "over one thousand tables"....

Here is a block of code that will retrieve the names of all tables that have those 8 columns in them and then search for CODE = M1001 in each of those tables and if it exists put the record into the temp table and then select all of the results....

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    BEGIN
        DROP TABLE #Results
    END

CREATE TABLE #Results (
    AutoID INT IDENTITY(1,1)
    ,Code CHAR(5)
    ,Designer VARCHAR(15)
    ,Brand VARCHAR(15)
    ,Gender CHAR(1)
    ,Size DECIMAL(3,1)
    ,Unit VARCHAR(5)
    ,Type CHAR(2)
    ,TOT INT
)

DECLARE @TableName NVARCHAR(513)

DECLARE CursorName CURSOR FOR
SELECT
    QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) as TableName
FROM
    sys.columns c
    INNER JOIN sys.tables t
    ON c.object_id = t.object_id
WHERE
    c.name IN ('CODE','DESIGNER','BRAND','GENDER','SIZE','UNIT','TYPE','TOT')
GROUP BY
    QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name)
HAVING
    COUNT(DISTINCT c.name) = 8

OPEN CursorName

FETCH NEXT FROM CursorName
INTO @TableName

    WHILE @@FETCH_STATUS = 0
    BEGIN

        DECLARE @SQL NVARCHAR(MAX)
        SET @SQL = '
        INSERT INTO #Results (Code,Designer,Brand,Gender,Size,Unit,Type,TOT)
          SELECT *
          FROM
             ' + @TableName + '
          WHERE
             CODE = ''M1001'''

          EXECUTE (@SQL)

        FETCH NEXT FROM CursorName
        INTO @TableName
    END

CLOSE CursorName
DEALLOCATE CursorName


SELECT *
FROM
    #Results