Home T-SQL context issue
Reply: 0

T-SQL context issue

user5166
1#
user5166 Published in July 18, 2018, 10:30 pm

I have created a script to compare the tables of two databases to find differences. I want to be able to manually set two variables (one for each database) and have the 'USE' statement use the variable value for one of the databases but it changes context and does work correctly.

This is what I am trying to use to populating a variable (@Use) with the USE code to execute

--set @Use = 'USE ' + @NewProdDB + ';SELECT name FROM sys.tables'

--EXEC (@Use)

This is the entire query:

--========================================================================================================

--  Used to find table values in the test upgrade system database not found the newly upgraded system
--  database. Make sure change the system database names before running the query.

--  Also, select the upgraded database to run against

--========================================================================================================



DECLARE @NewProdDB VARCHAR(100)
DECLARE @TestUpgradeDB VARCHAR(100)
DECLARE @Columns VARCHAR (MAX)
DECLARE @Query VARCHAR(MAX)
DECLARE @ResultsTest VARCHAR(MAX)
DECLARE @SetResultsCursor NVARCHAR(MAX)
DECLARE @Fetcher NVARCHAR(MAX)
DECLARE @Use NVARCHAR(50)
DECLARE @ListOfTables VARCHAR(100)
DECLARE @WTF NVARCHAR(max)
DECLARE @rescanCode nvarchar(max)

/*               Enter Upgraded system DB here        */
SET @NewProdDB = 'zSBSSYS'

/*               Enter Test Upgrade system DB here   */
SET @TestUpgradeDB = 'TESTzSBSSYS'


--set @Use = 'USE ' + @NewProdDB + ';SELECT name FROM sys.tables'

--EXEC (@Use)

SET NOCOUNT ON

set @rescanCode = 'DECLARE recscan CURSOR FOR 
SELECT TABLE_NAME FROM ' + @TestUpgradeDB + '.INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME NOT LIKE ''Mbf%'' AND TABLE_TYPE = ''BASE TABLE''
ORDER BY TABLE_NAME' 

exec (@rescanCode)
OPEN recscan 
FETCH NEXT FROM recscan INTO @ListOfTables


WHILE @@fetch_status = 0     
    BEGIN 
        BEGIN TRY


         /* START Table column query */
         Declare @Table varchar(60) 

         set @Table = @ListOfTables

         set @table = (SELECT top 1 name FROM sys.tables where name = @Table)
         DECLARE 
          @sql1 nvarchar(max) = ''
         ,@INTOColumns NVARCHAR(MAX)=''
         ,@Where nvarchar(max) = ''
         ,@ColumnID Int 
         ,@Column Varchar(8000) 
         ,@Type Varchar(8000) 
         ,@Length Int 
         ,@del1 varchar(2) = ','
         ,@CRLF bit = 1
         ,@aliasfield varchar(5) = '[a].'

         DECLARE CSR_Attributes CURSOR FOR
         SELECT 
         [ColumnID] = Col.column_id,
         [Column] = Col.Name,
         [Type] = [types].name,
         [Length] = Col.max_length
         FROM sys.objects Obj, sys.columns Col, sys.types [types]
         WHERE Obj.OBJECT_ID = Col.OBJECT_ID AND Obj.TYPE = 'U'
         AND Col.system_type_id = [types].system_type_id
         AND Obj.name = @table
         AND Col.name <> 'tstamp'
         ORDER BY Obj.name, Col.column_id, Col.name
         OPEN CSR_Attributes
         FETCH NEXT FROM CSR_Attributes INTO @ColumnID, @Column, @Type, @Length
         WHILE @@FETCH_STATUS = 0
         BEGIN 
         set @sql1 += @column + @del1
         set @Where += 'b.' + @column + '=a.' + @column + ' and '
         set @INTOColumns += '@INTOcol,' 
         FETCH NEXT FROM CSR_Attributes INTO @ColumnID, @Column, @Type, @Length
         END
         CLOSE CSR_Attributes
         DEALLOCATE CSR_Attributes

         SET @Columns = SUBSTRING(@sql1,1,len(@sql1)-1) -- get rid of last comma
         SET @Where = SUBSTRING(@Where,1,len(@Where)-4) -- get rid of last 'and'
         SET @INTOColumns = SUBSTRING(@INTOColumns,1,len(@INTOColumns)-1) -- get rid of last comma

         /* END Table column query */

         /*   Create SELECT statement here  */

         SET @ResultsTest='SELECT TOP 1 ' + @Columns + '
         FROM ' + @TestUpgradeDB + '..' + @Table + ' A 
         WHERE NOT EXISTS
         (SELECT ' + @Columns + ' 
         FROM ' + @NewProdDB + '..' + @Table + ' B WHERE ' + @Where + ')'



         SET @SetResultsCursor = 'DECLARE DataReturned CURSOR FOR ' + @ResultsTest  

         exec (@SetResultsCursor)
         OPEN DataReturned

         SET @Fetcher = 'DECLARE @INTOcol NVARCHAR(Max)
         FETCH NEXT FROM DataReturned INTO ' + @INTOColumns

         exec (@Fetcher)

         if @@FETCH_STATUS = 0

             begin

             CLOSE DataReturned

                DEALLOCATE DataReturned

                SET @Query='SELECT ' + @Columns + '
                FROM ' + @TestUpgradeDB + '..' + @Table + ' A WHERE NOT EXISTS
                (SELECT ' + @Columns + ' FROM ' + @NewProdDB + '..' + @Table + ' B WHERE ' + @Where + ')'



                select @Table
                exec (@Query)

            end
        else
            begin

                CLOSE DataReturned
                DEALLOCATE DataReturned
            end


    end try
    begin catch
        --SELECT ERROR_MESSAGE() AS ErrorMessage;  

    end catch

    FETCH NEXT FROM recscan INTO @ListOfTables

end 

CLOSE recscan
DEALLOCATE recscan
SET NOCOUNT OFF
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.321533 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO