Building queries using information_schema views in sql server

Question

Am trying to write query in sql server 2014
In my DB - I have these 3 tables
SELECT table_name, column_name FROM INFORMATION_SCHEMA.COLUMNS

Table_Name Column_name Data_Type
emp emp_no nvarchar(20)
emp empname nvarchar(20)
emp sal int
emp birth_date datetime
dept deptno nvarchar(20)
dept deptname nvarchar(20)
location locno nvarchar(20)
location lname nvarchar(20)

Am expecting the below output with the column - query and
result should fetch 3 rows as below.

query
select emp_no,empname,sal,cast(birth_date as date) birth_date from emp
select deptno,deptname from dept
select locno,lname from location

Answer 1

Hi @AbdulWahab Khan
Check this query:

;WITH CTE AS   
(  
 SELECT Table_Name,ORDINAL_POSITION,CASE WHEN Data_Type ='datetime' THEN 'cast('+Column_Name+' as date)'ELSE Column_Name END AS Column_Name  
 FROM INFORMATION_SCHEMA.COLUMNS  
)  
SELECT 'select '   
      + STUFF((select ', ' + column_name from CTE  
               where Table_Name = c.Table_Name  
               order by ORDINAL_POSITION  
               for xml path('') ), 1, 2, '')   
	  + ' from '   
	  + Table_Name AS query  
FROM CTE C  
GROUP BY Table_Name  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

For the original question, try something like this:

select   
    'select ' +   
    stuff((  
        select ', ' + quotename(column_name)  
        from INFORMATION_SCHEMA.COLUMNS  
        where table_name = c.table_name  
        order by ORDINAL_POSITION  
        for xml path('')  
    ), 1, 2, '') +  
    ' from ' + quotename(table_name) as query  
from INFORMATION_SCHEMA.COLUMNS c  
group by table_name