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