Sql – How to select column names from multiple tables in SQL server 2008 by avoiding certain columns which reoccur in all tables

sqlsql-serversql-server-2008tsql

I have a database that stores the complete details of the employees of a firm.
I need to select column names from multiple tables in the database which when executed would provide me the columns required to list specific details. So, I tried the following query which returns the columns of the specified table only.

SELECT TABLE_NAME,COLUMN_NAME
FROM mydatabase.INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME=N'TBLemppersonaldetails'

All the tables have a Column named 'EmployeeId' which can be considered as a primary key.
Now, how could i get all required columns from all the tables by avoiding certain columns such as Update Date,Mode that repeats in all the tables.
I would prefer using 'Inner Join' to join all the tables.
For example consider the tables below:

          Table1                                            Table2
EmployeeId     Name     Address     Mode      EmployeeId   Gender   BloodGroup     Mode
----------------------------------------     -------------------------------------------
   001         abc      No.9,vv      1          001         Male       O+ve          1
                        street,

Considering the example above, I require the columns EmployeeId,Name,Address,Gender,BloodGroup to be listed after the execution of the query while excluding the column named 'Mode' from both the tables.
I request you to consider another scenario where the so called 'PrimaryKey' constraint doesn't exist for the 'EmployeeId' Column.
PS: I am using SQL Server 2008

Best Solution

You need to dynamically create a SQL statement and then run that command

Update 22.06.2013

IF OBJECT_ID('dbo.Table1') IS NOT NULL DROP TABLE dbo.Table1
CREATE TABLE dbo.Table1
 (
  EmployeeId int PRIMARY KEY,
  Name varchar(100),
  Address varchar(100),
  Mode int
  )

INSERT dbo.Table1
VALUES(1, 'abc', 'Address', 1)  

IF OBJECT_ID('dbo.Table2') IS NOT NULL DROP TABLE dbo.Table2
CREATE TABLE dbo.Table2
 ( 
  EmployeeId int PRIMARY KEY,
  Gender varchar(100),
  BloodGroup varchar(100),
  Mode int
  )  

INSERT dbo.Table2
VALUES(1, 'Male', 'O+ve', 1)

DECLARE @TablesWithAlias TABLE([schema] varchar(10), name varchar(20), alias varchar(10))
INSERT @TablesWithAlias
VALUES('dbo', 'Table1', 't1.'),
      ('dbo', 'Table2', 't2.')
DECLARE @dsql nvarchar(max)   
SELECT @dsql = COALESCE(@dsql + ',', '') + MIN(twa.alias) + CASE WHEN o.IsPrimaryKey IS NOT NULL THEN c.name ELSE c.name END
FROM sys.schemas s
  JOIN sys.tables t ON s.schema_id = t.schema_id
  JOIN sys.columns c ON t.object_id = c.object_id
  OUTER APPLY (
               SELECT CASE WHEN OBJECTPROPERTY(object_id(constraint_name), 'IsPrimaryKey') = 1 THEN 1 END AS IsPrimaryKey
               FROM information_schema.key_column_usage k
               WHERE s.name = k.table_schema AND t.name = k.table_name
                 AND c.name = k.COLUMN_NAME
               ) o

  JOIN @TablesWithAlias twa ON s.name = twa.[schema] AND t.name = twa.name               
GROUP BY c.column_id, c.name, o.IsPrimaryKey
HAVING COUNT(*) = 1 OR o.IsPrimaryKey IS NOT NULL
ORDER BY c.column_id
PRINT @dsql

--Your 'Inner Join' to join all the tables.    
SET @dsql = 
  'SELECT ' + @dsql +
  ' FROM dbo.Table1 t1 JOIN dbo.Table2 t2 ON t1.EmployeeId = t2.EmployeeId '
PRINT @dsql
EXEC sp_executesql @dsql