Dynamic query to select all the columns except PK from multiple table using SQL Query

Question

The below script is throwing an error of Duplicate StudentID column. We need SQL script so we can select all column except the PK column.
We can't simple write those column names as the script is going to be dynamic.

SELECT 
s.*,
c.studentId,
c.SYS_CHANGE_VERSION, 
c.SYS_CHANGE_OPERATION, 
c.SYS_CHANGE_COLUMNS
FROM @{item().TABLE_SCHEMA}.@{item().TABLE_NAME} AS s 
RIGHT OUTER JOIN CHANGETABLE(
CHANGES @{item().TABLE_SCHEMA}.@{item().TABLE_NAME}, 
@{activity('lkupLastChngTrackVer').output.firstRow.SYS_CHANGE_VERSION}) as c 
ON s.[studentId] = c.[studentId] 
where c.SYS_CHANGE_VERSION <= 
@{activity('lkupCurChngTrackVer').output.firstRow.CurrentChangeTrackingVersion} 

This script was used in ADF, is it possible to convert this script completely in SQL.As there are some variable which causing issue in the conversion.

Answer 1

Hello @Sarvesh Pandey ,

Thanks for the ask and using the forum .

If the ask is just to exclude the PK column , you can reference the below query and make adjustment to your query ,hope fully it helps .

CREATE TABLE dbo.footest2   
(  
id int identity(1,1)   
,name varchar(100)   
,state varchar(100)   
,primary key (id)  
)  
**select c.name from sys.columns C   
JOIN sys.tables T   
on C.object_id = t.object_id  
 where t.name ='footest2'  
 and c.name not in (select column_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE  
		where TABLE_NAME =t.name )**   

Thanks Himanshu
Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Answer 2

Hi @Sarvesh Pandey ,

Please also refer below and check whether it is helpful to you.

CREATE TABLE dbo.testtable   
 (  
 id int identity(1,1)  primary key  
 ,name varchar(100)   
 ,updatedate date  
 )  
  
DECLARE @TABLE_SCHEMA Varchar(100) ='dbo'  
,@TABLE_Name Varchar(100)='testtable'  
,@ColList Varchar(1000)  
, @SQLStatment VARCHAR(4000)  
SET @ColList = ''  
select @ColList = @ColList + Name + ' , ' from syscolumns where id = object_id(@TABLE_SCHEMA+'.'+@TABLE_Name) AND Name not in  
(select col.name    
    from sys.tables tab  
    inner join sys.indexes pk  
        on tab.object_id = pk.object_id   
        and pk.is_primary_key = 1  
    inner join sys.index_columns ic  
        on ic.object_id = pk.object_id  
        and ic.index_id = pk.index_id  
    inner join sys.columns col  
        on pk.object_id = col.object_id  
        and col.column_id = ic.column_id  
     where tab.object_id = object_id(@TABLE_SCHEMA+'.'+@TABLE_Name) )  
SELECT @SQLStatment = 'SELECT ' + Substring(@ColList,1,len(@ColList)-1) + ' From '+@TABLE_SCHEMA+'.'+@TABLE_Name  
PRINT @SQLStatment  

Output:
SELECT name , updatedate From dbo.testtable

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table