nFdiCPQE

1--set @Use = 'USE ' + @NewProdDB + ';SELECT name FROM sys.tables'
2
3--EXEC (@Use)
4	
5--========================================================================================================
6
7--  Used to find table values in the test upgrade system database not found the newly upgraded system
8--  database. Make sure change the system database names before running the query.
9
10--  Also, select the upgraded database to run against
11
12--========================================================================================================
13
14
15
16DECLARE @NewProdDB VARCHAR(100)
17DECLARE @TestUpgradeDB VARCHAR(100)
18DECLARE @Columns VARCHAR (MAX)
19DECLARE @Query VARCHAR(MAX)
20DECLARE @ResultsTest VARCHAR(MAX)
21DECLARE @SetResultsCursor NVARCHAR(MAX)
22DECLARE @Fetcher NVARCHAR(MAX)
23DECLARE @Use NVARCHAR(50)
24DECLARE @ListOfTables VARCHAR(100)
25DECLARE @WTF NVARCHAR(max)
26DECLARE @rescanCode nvarchar(max)
27
28/*               Enter Upgraded system DB here        */
29SET @NewProdDB = 'zSBSSYS'
30
31/*               Enter Test Upgrade system DB here   */
32SET @TestUpgradeDB = 'TESTzSBSSYS'
33
34
35--set @Use = 'USE ' + @NewProdDB + ';SELECT name FROM sys.tables'
36
37--EXEC (@Use)
38
39SET NOCOUNT ON
40
41set @rescanCode = 'DECLARE recscan CURSOR FOR 
42SELECT TABLE_NAME FROM ' + @TestUpgradeDB + '.INFORMATION_SCHEMA.TABLES 
43WHERE TABLE_NAME NOT LIKE ''Mbf%'' AND TABLE_TYPE = ''BASE TABLE''
44ORDER BY TABLE_NAME' 
45
46exec (@rescanCode)
47OPEN recscan 
48FETCH NEXT FROM recscan INTO @ListOfTables
49
50
51WHILE @@fetch_status = 0     
52    BEGIN 
53        BEGIN TRY
54
55
56         /* START Table column query */
57         Declare @Table varchar(60) 
58
59         set @Table = @ListOfTables
60
61         set @table = (SELECT top 1 name FROM sys.tables where name = @Table)
62         DECLARE 
63          @sql1 nvarchar(max) = ''
64         ,@INTOColumns NVARCHAR(MAX)=''
65         ,@Where nvarchar(max) = ''
66         ,@ColumnID Int 
67         ,@Column Varchar(8000) 
68         ,@Type Varchar(8000) 
69         ,@Length Int 
70         ,@del1 varchar(2) = ','
71         ,@CRLF bit = 1
72         ,@aliasfield varchar(5) = '[a].'
73
74         DECLARE CSR_Attributes CURSOR FOR
75         SELECT 
76         [ColumnID] = Col.column_id,
77         [Column] = Col.Name,
78         [Type] = [types].name,
79         [Length] = Col.max_length
80         FROM sys.objects Obj, sys.columns Col, sys.types [types]
81         WHERE Obj.OBJECT_ID = Col.OBJECT_ID AND Obj.TYPE = 'U'
82         AND Col.system_type_id = [types].system_type_id
83         AND Obj.name = @table
84         AND Col.name <> 'tstamp'
85         ORDER BY Obj.name, Col.column_id, Col.name
86         OPEN CSR_Attributes
87         FETCH NEXT FROM CSR_Attributes INTO @ColumnID, @Column, @Type, @Length
88         WHILE @@FETCH_STATUS = 0
89         BEGIN 
90         set @sql1 += @column + @del1
91         set @Where += 'b.' + @column + '=a.' + @column + ' and '
92         set @INTOColumns += '@INTOcol,' 
93         FETCH NEXT FROM CSR_Attributes INTO @ColumnID, @Column, @Type, @Length
94         END
95         CLOSE CSR_Attributes
96         DEALLOCATE CSR_Attributes
97
98         SET @Columns = SUBSTRING(@sql1,1,len(@sql1)-1) -- get rid of last comma
99         SET @Where = SUBSTRING(@Where,1,len(@Where)-4) -- get rid of last 'and'
100         SET @INTOColumns = SUBSTRING(@INTOColumns,1,len(@INTOColumns)-1) -- get rid of last comma
101
102         /* END Table column query */
103
104         /*   Create SELECT statement here  */
105
106         SET @ResultsTest='SELECT TOP 1 ' + @Columns + '
107         FROM ' + @TestUpgradeDB + '..' + @Table + ' A 
108         WHERE NOT EXISTS
109         (SELECT ' + @Columns + ' 
110         FROM ' + @NewProdDB + '..' + @Table + ' B WHERE ' + @Where + ')'
111
112
113
114         SET @SetResultsCursor = 'DECLARE DataReturned CURSOR FOR ' + @ResultsTest  
115
116         exec (@SetResultsCursor)
117         OPEN DataReturned
118
119         SET @Fetcher = 'DECLARE @INTOcol NVARCHAR(Max)
120         FETCH NEXT FROM DataReturned INTO ' + @INTOColumns
121
122         exec (@Fetcher)
123
124         if @@FETCH_STATUS = 0
125
126             begin
127
128             CLOSE DataReturned
129
130                DEALLOCATE DataReturned
131
132                SET @Query='SELECT ' + @Columns + '
133                FROM ' + @TestUpgradeDB + '..' + @Table + ' A WHERE NOT EXISTS
134                (SELECT ' + @Columns + ' FROM ' + @NewProdDB + '..' + @Table + ' B WHERE ' + @Where + ')'
135
136
137
138                select @Table
139                exec (@Query)
140
141            end
142        else
143            begin
144
145                CLOSE DataReturned
146                DEALLOCATE DataReturned
147            end
148
149
150    end try
151    begin catch
152        --SELECT ERROR_MESSAGE() AS ErrorMessage;  
153
154    end catch
155
156    FETCH NEXT FROM recscan INTO @ListOfTables
157
158end 
159
160CLOSE recscan
161DEALLOCATE recscan
162SET NOCOUNT OFF