Skip to content
Advertisement

How to Pick Column names from table

I have tables which have columns and tables

Name
EmpId
Empname
DeptID
DeptName
Employee
Department

I have INFORMATION_SCHEMA.COLUMNS to get Table names

Script :

I have got table names

Select T.Name from  INFORMATION_SCHEMA.COLUMNS I 
INNER JOIN @tbl_columns T 
ON T.name = I.Table_name

Here I got table names

Output :

Name
Employee
Department

I need to get output which table have which column

Tbl_Name    ColName 
Employee    EmpId
Employee    Empname
Department  DeptID
Department  DeptName

Advertisement

Answer

I believe you want to connect table name and column name from @tbl_column. If yes I think you can use following script:

SELECT   T2.NAME
        ,C.NAME 
FROM SYS.TABLES T1 
INNER JOIN @tbl_columns T2 ON T1.name = T2.NAME
INNER JOIN (SELECT C2.*,C1.object_id 
            FROM SYS.COLUMNS C1 
            INNER JOIN @tbl_columns C2 ON C1.name = C2.NAME
            ) C ON C.object_id = T1.object_id
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement