I have tables which have columns and tables
x
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