Forum Discussion
Kiru_2024
Apr 15, 2024Copper Contributor
Must declare even if I have already declared - what am i doing wrong?
Below is the script and its throwing "Must declare the scalar variable "@sql"." error. EXEC sp_MSforeachdb ' IF OBJECT_ID(''tempdb..##FKConstraints'') IS NOT NULL DROP TABLE ##FKConstraints...
SivertSolem
Apr 17, 2024Iron Contributor
I'm not able to debug the entirety of your script, but it would appear you have an issue with nested strings and escaping the ' character.
- SivertSolemApr 17, 2024Iron Contributor
Your script
EXEC sp_MSforeachdb ' IF OBJECT_ID(''tempdb..##FKConstraints'') IS NOT NULL DROP TABLE ##FKConstraints; CREATE TABLE ##FKConstraints ( ServerName VARCHAR(40), DatabaseName VARCHAR(20), SQLText VARCHAR(MAX) ); DECLARE @FinalSQL NVARCHAR(MAX) = N'' IF DB_ID() > 4 BEGIN DECLARE @sql VARCHAR(MAX) = '' SELECT @sql = @sql + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + ''ALTER TABLE ['' + s.name + '']'' + ''.'' + ''['' + o.name + '']'' + ''WITH CHECK CHECK CONSTRAINT'' + i.name + CHAR(13) + CHAR(10) FROM sys.foreign_keys i INNER JOIN sys.objects o ON i.parent_object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE i.is_not_trusted = 1 AND is_not_for_replication = 0; IF @@ROWCOUNT > 0 INSERT INTO ##FKConstraints ( ServerName, DatabaseName, SQLText ) VALUES (@@SERVERNAME, DB_NAME(), @sql); END; --SELECT * FROM ##FKConstraints SELECT @FinalSQL = N''USE'' + N'' ''+ DatabaseName + N'' '' + CHAR(13) + CHAR(10) + SQLText + CHAR(13) + CHAR(10) FROM ##FKConstraints; EXEC sp_executesql @FinalSQL;'
What's executed by sp_MSForEachDb
IF OBJECT_ID('tempdb..##FKConstraints') IS NOT NULL DROP TABLE ##FKConstraints; CREATE TABLE ##FKConstraints ( ServerName VARCHAR(40), DatabaseName VARCHAR(20), SQLText VARCHAR(MAX) ); DECLARE @FinalSQL NVARCHAR(MAX) = N' IF DB_ID() > 4 BEGIN DECLARE @sql VARCHAR(MAX) = ' SELECT @sql = @sql + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + 'ALTER TABLE [' + s.name + ']' + '.' + '[' + o.name + ']' + 'WITH CHECK CHECK CONSTRAINT' + i.name + CHAR(13) + CHAR(10) FROM sys.foreign_keys i INNER JOIN sys.objects o ON i.parent_object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE i.is_not_trusted = 1 AND is_not_for_replication = 0; IF @@ROWCOUNT > 0 INSERT INTO ##FKConstraints ( ServerName, DatabaseName, SQLText ) VALUES (@@SERVERNAME, DB_NAME(), @sql); END; --SELECT * FROM ##FKConstraints SELECT @FinalSQL = N'USE' + N' '+ DatabaseName + N' ' + CHAR(13) + CHAR(10) + SQLText + CHAR(13) + CHAR(10) FROM ##FKConstraints;
Pay particular attention to the area around "Declare @sql" on line 12 and how that breaks when '' is converted back to '.