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

Question

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

please give a small example of the SQL you want to generate if you you had to write it manually. Say using two tables. — Preet Sangha
– Preet Sangha, Commented Jun 3, 2013 at 5:56
@RaviSingh.....What do i do if the Column named 'EmployeeId' or any other column cannot be assigned the primary key in certain tables? — Rohit Kiran
– Rohit Kiran, Commented Jun 3, 2013 at 7:35

Oleksandr Fedorenko · Accepted Answer · 2013-06-22 15:31:26Z

1

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

edited Jun 22, 2013 at 15:31

answered Jun 3, 2013 at 7:42

Oleksandr Fedorenko

17k6 gold badges42 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rohit Kiran Over a year ago

Could you provide an edit on your answer by avoiding the Primary Key Constraint? I get an error message :Ambiguous column name 'EmployeeId', as it repeats in several tables.

Oleksandr Fedorenko Over a year ago

Could you show an your PRINT statement, please? And I'll try to help you

Rohit Kiran Over a year ago

Well this is what i got under the 'Message' tab after executing the query: SELECT EmployeeId,EmpIloyeed,Image,FirstName.....etc. Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'EmployeeId'. Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'EmpId'.

Oleksandr Fedorenko Over a year ago

Ok, @Rohit Kiran;) Answer is updated. Note, aliases of table variable corresponds to the an aliases in the main query

Devart · Accepted Answer · 2013-06-04 06:41:22Z

1

Try this one -

DDL:

SET NOCOUNT ON;

IF OBJECT_ID (N'dbo.Table1') IS NOT NULL 
    DROP TABLE dbo.Table1 

CREATE TABLE dbo.Table1 
(
      EmployeeId INT
    , Name VARCHAR(30)
    , [Address] VARCHAR(150)
    , Mode INT
    , Img IMAGE
    , UpdateDate DATETIME
    , IpAddress VARCHAR(20)
) 

IF OBJECT_ID (N'dbo.Table2') IS NOT NULL
    DROP TABLE dbo.Table2

CREATE TABLE dbo.Table2 
(
      EmployeeId INT
    , Gender VARCHAR(6)
    , BloodGroup VARCHAR(20)
    , Mode INT
)

INSERT INTO dbo.Table1 (EmployeeId, Name, [Address], Mode, UpdateDate, IpAddress) 
VALUES ('001', 'abc', 'No.9,vv street', 1,'06/04/2013 12:00:00','192.168.0.1')

INSERT INTO dbo.Table2 (EmployeeId, Gender, BloodGroup, Mode)
VALUES ('001', 'Male', 'O+ve', 1)

Query:

DECLARE @SQL NVARCHAR(MAX)

;WITH cte AS 
(
    SELECT 
          column_name = '[' + c.name + ']'
        , table_name = '[' + s.name + '].[' + o.name + ']'
        , [type_name] = t.name
    FROM sys.columns c WITH (NOLOCK)
    JOIN sys.objects o WITH (NOLOCK) ON c.[object_id] = o.[object_id]
    JOIN sys.schemas s WITH (NOLOCK) ON o.[schema_id] = s.[schema_id]
    JOIN sys.types t WITH (NOLOCK) ON c.user_type_id = t.user_type_id
    WHERE o.name IN ('Table1', 'Table2')
        AND s.name = 'dbo'
        AND o.[type] = 'U'
        AND c.name NOT IN ('Mode', 'UpdateDate', 'IpAddress')  
), unicol AS (
    SELECT TOP 1 column_name 
    FROM cte 
    GROUP BY cte.column_name
    HAVING COUNT(cte.column_name) > 1
), cols AS 
(
    SELECT DISTINCT column_name, [type_name] 
    FROM cte    
), tbl AS 
(
    SELECT DISTINCT table_name
    FROM cte
), rs AS 
(
    SELECT 
          tbl.table_name
        , column_name = ISNULL(cte.column_name, cols.column_name + ' = NULL')
    FROM cols
    CROSS JOIN tbl
    LEFT JOIN cte ON cols.column_name = cte.column_name AND cte.table_name = tbl.table_name
), rs2 AS (
    SELECT uni = ' UNION ALL' + CHAR(13) + 'SELECT ' + STUFF((
        SELECT ', ' + rs.column_name
        FROM rs
        WHERE tbl.table_name = rs.table_name
        GROUP BY rs.column_name
        ORDER BY rs.column_name
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + 
        ' FROM ' + table_name
    FROM tbl
) 
SELECT @SQL = 'SELECT 
' + STUFF((
    SELECT CHAR(13) + ', ' + ISNULL(unicol.column_name, cols.column_name + ' = MAX(' 
    + CASE 
        WHEN [type_name] = 'image' THEN 'CONVERT(VARBINARY(MAX), ' + cols.column_name + ')' 
        WHEN [type_name] = 'bit' THEN 'CAST(' + cols.column_name + ' AS CHAR(1))' 
        ELSE cols.column_name 
        END + ')')
    FROM cols
    LEFT JOIN unicol ON cols.column_name = unicol.column_name
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ' ')
    + ' 
FROM 
(' + STUFF((
    SELECT CHAR(10) + uni
    FROM rs2
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 11, '') + CHAR(13) + 
    ') t 
GROUP BY ' + (SELECT column_name FROM unicol)

PRINT @SQL

EXECUTE sys.sp_executesql @SQL

Output:

Address            BloodGroup   EmployeeId  Gender Img      Name
------------------ ------------ ----------- ------ -------- -------
No.9,vv street     O+ve         1           Male   NULL     abc

edited Jun 4, 2013 at 6:41

answered Jun 3, 2013 at 6:23

Devart

120k23 gold badges172 silver badges189 bronze badges

4 Comments

Rohit Kiran Over a year ago

@devart...Some of the tables have datatype as Image. So your query returns the message; "Operand data type image is invalid for max operator." Any solution?

Rohit Kiran Over a year ago

@Devart..It Works.. But certain fields such as 'Mode,UpdateDate' should be avoided. Can i specify those columns in the query?

Rohit Kiran Over a year ago

@Devart.... DLL: SET NOCOUNT ON IF OBJECT_ID (N'dbo.Table1') IS NOT NULL DROP TABLE dbo.Table1 CREATE TABLE dbo.Table1 (EmployeeId INT, Name VARCHAR(30), [Address] VARCHAR(150), Mode INT, Img IMAGE,UpdateDate datetime,IpAddress varchar(20)) INSERT INTO dbo.Table1 (EmployeeId, Name, [Address], Mode,UpdateDate,IpAddress) VALUES ('001', 'abc', 'No.9,vv street', 1,06/04/2013 12:00:00,192.168.0.1) Consider the same entries for the columns Mode,UpdateDate & IpAddress. These columns need not appear in the final selection.

Rohit Kiran Over a year ago

@Devart...Almost Done....The Column EmployeeId repeats twice in the output as this column is present in both Table1 and Table2; but i need the column to occur only once in the output.

Anvesh · Accepted Answer · 2013-06-03 06:19:19Z

0

Search your column name using this :

 SELECT OBJECT_NAME (object_id ),*
 FROM sys.columns
 WHERE name LIKE 'ColumnName'

answered Jun 3, 2013 at 6:19

Anvesh

7,8073 gold badges48 silver badges45 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

4 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related