what is wrong in this code

[washaw]

This thing is giving me a headache,

in our database we have 200 table out of wich 119 have additional two columns "Inserted" and "modified"
which keeps track of which rows are inserted and which are modified (N.B all rows in a table are either inserted or modified or non).

I am planning to count how many tables are inserted, and how many are modified for the 119 tables with columns inserted and modified

I wrote this query but don't know where I messed it up

CREATE TABLE #rowcount (rowcnt INT)

EXEC sp_MSForEachTable 
      'IF EXISTS(SELECT c.table_name, c.column_name
                 FROM information_schema.columns c
		     INNER JOIN information_schema.tables t 
		      ON c.table_name = t.table_name 
	         WHERE c.table_name = 
                        SUBSTRING(''?'', (CHARINDEX(''['',''?'', 2) + 1), ((LEN(''?'') - (CHARINDEX(''['',''?'', 2) + 1))))
		  AND c.column_name = ''to_be_transferred''
		  AND t.table_type = ''BASE TABLE''
                )
                BEGIN
                  IF EXISTS(SELECT * FROM ? 
                       WHERE to_be_transferred IS NOT NULL) 
                      INSERT INTO #rowcount(rowcnt) SELECT 0
                  ELSE IF EXISTS(SELECT * FROM ? 
                           WHERE to_be_transferred IS NULL) 
                      INSERT INTO #rowcount(rowcnt) SELECT 1
                  ELSE IF EXISTS(SELECT * FROM ? 
                          WHERE to_be_transferred = ''YES'')
                     INSERT INTO #rowcount(rowcnt) SELECT 2 
      END '

select  (SELECT COUNT(ROWCNT) 
         from #rowcount
         WHERE ROWCNT = 0) AS INSERTED,
         (SELECT COUNT(ROWCNT) 
         from #rowcount
         WHERE ROWCNT = 1) AS MODIFIED,
         (SELECT COUNT(ROWCNT) 
          from #rowcount
          WHERE ROWCNT = 2) AS N0N

DROP TABLE #rowcount

It is giving me an error "invalid column name inserted"

but the first IF statement is to filter out those tables with these columns Inserted and modified

Thanks

 

[PatriciaObreja]

I think that you cannot give to a column the reserved name Inserted, at least try to put it in []:
[Inserted]

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[washaw]

This code might be more clear,

why is it erroring out
'invalid column name to_be_transferred', I thought the if statement only filters those tables with to_be_transferred columns. see the code

 EXEC sp_MSForEachTable 'IF EXISTS(SELECT c.table_name, c.column_name
                                  FROM information_schema.columns c
		                               INNER JOIN information_schema.tables t 
			                           ON c.table_name = t.table_name 
	                              WHERE c.table_name = SUBSTRING(''?'', (CHARINDEX(''['',''?'', 2) + 1), 
                                                ((LEN(''?'') - (CHARINDEX(''['',''?'', 2) + 1))))
		                                AND c.column_name = ''to_be_transferred''
		                                AND t.table_type = ''BASE TABLE''
                                 )
                        BEGIN
                           
                            SELECT  * FROM ? 
                            WHERE to_be_transferred IS NOT NULL  
                        END '

any suggestion

THanks

 

[washaw]

Any suggestion, I have been struggling with this for two days now

THanks

 

[washaw]

I was able to solve this problem

I used a tem table to load every data from the tables and query the temp table

here is the modification od the code

EXEC sp_MSForEachTable 'IF EXISTS(SELECT c.table_name, c.column_name
                                  FROM information_schema.columns c
                                       INNER JOIN information_schema.tables t 
                                       ON c.table_name = t.table_name 
                                  WHERE c.table_name = SUBSTRING(''?'', (CHARINDEX(''['',''?'', 2) + 1), 
                                                ((LEN(''?'') - (CHARINDEX(''['',''?'', 2) + 1))))
                                        AND c.column_name = ''to_be_transferred''
                                        AND t.table_type = ''BASE TABLE''
                                 )
                        BEGIN
                            select * into #tmpTable from ?
                            SELECT  * FROM #tmpTable 
                            WHERE to_be_transferred IS NOT NULL  
                        END '

Thanks