CODEWITHSUNDEEP
sqlsql-server
LarAnto
LarAnto

Reputation: 136

How to list the column names in a query

I'm working with sql server and I was asked to give grant solely to the table column's involved in a query.

So let's suppose I had this query Q to perform an extraction:

SELECT 
    A.C1,
    B.C1,
    C.C1 
FROM   
    TABLE1 A 
INNER JOIN 
    TABLE2 B ON A.C2 = B.C2 
INNER JOIN 
    TABLE3 C ON B.C3 = C.C2

I had to give SELECT permission only to columns:

   A.C1
   A.C2
   B.C1
   B.C2
   B.C3
   C.C1
   C.C2

Now I have to parse in a boring human way the query, to collect the columns.

Is there a way, some system query way, to "query the query"? A way to put the query Q in input and to get the list of the involved columns in output?

Thank you very much.

Upvotes: 3

Views: 188

Answers (1)

Laur Ivan
Laur Ivan

Reputation: 4177

IMHO you should create a view containing the actual restricted result.

Something like:

 CREATE VIEW view_name AS
 SELECT A.C1,
      B.C1,
      C.C1 
 FROM   TABLE1 A 
 INNER JOIN TABLE2 B 
       ON A.C2 = B.C2 
          t_line.dbo.is_pagamentorate.parentlink_recid 
 INNER JOIN TABLE3 C 
       ON B.C3 = C.C2

Upvotes: 3

Related Questions