I am getting a set of table names from sys tables and trying to loop through them to get columns from them and then append to string in dynamic SQL.
I am having issues with syntax. Please help me. am trying to pass @tablename to the select * statement and it doesnt like it. Please help me
DECLARE @col [VARCHAR](100);
DECLARE @TableCursor AS CURSOR;
SET
@TableCursor = CURSOR FOR
SELECT st.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES st
WHERE REPLACE(st.TABLE_NAME, 'TEST' + '_' + 'PIV' + '_', '') = 'Marketing_campaign'
AND st.TABLE_SCHEMA = 'DV';
DECLARE @Tablename VARCHAR(100);
OPEN @TableCursor;
FETCH NEXT FROM @TableCursor
INTO @Tablename;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @colCursor AS CURSOR;
SET @colCursor = CURSOR FOR
SELECT * FROM @Tablename; ****** this is where its complaining****
OPEN @colCursor;
FETCH NEXT FROM @colCursor
INTO @col;
WHILE @@FETCH_STATUS = 0
BEGIN
CLOSE @colCursor;
DEALLOCATE @colCursor;
END;
FETCH NEXT FROM @TableCursor
INTO @Tablename;
END;
CLOSE @TableCursor;
DEALLOCATE @TableCursor;