build and execute statement dynamically in SQL server

Question

Hello I'm trying to disable all foreign key constraints on a database as I'm loading in some data. As it's part of a monthly job I want to do it dynamically so it can handle any changes. I'm trying to do this without using a cursor or temp table. I have built the statement below but so far this only creates an alter statement whereas I want it to execute the alter statement

declare @SQLStatement as nvarchar(4000)
SET @SQLStatement = 'exec (''select ''''alter table prpcref_mjp''''        + ''''.'''' + b.name + ''''.'''' + parent_tbl.name +  '''' NOCHECK CONSTRAINT '''' +  fk.name
from prpcref_mjp.sys.foreign_keys fk
inner join prpcref_mjp.sys.all_objects parent_tbl
    on fk.parent_object_id = parent_tbl.object_id
inner join prpcref_mjp.sys.schemas b
    on fk.schema_id = b.schema_id'')'
EXECUTE sp_executesql @SQLStatement

Are you concerned about how to recreate the foreign key constraints after all of the data has been loaded? — user212514
– user212514, Commented May 18, 2016 at 5:56

Sufyan Jabr · Accepted Answer · 2016-05-18 16:41:13Z

1

Leave the exec out of the string:

 select exec('alter table prpcref_mjp''' + 
'';'.'''' + 
        b.name + ''''.'''' + parent_tbl.name +  
            '''' NOCHECK CONSTRAINT '''' +  fk.name
                from prpcref_mjp.sys.foreign_keys fk
                inner join prpcref_mjp.sys.all_objects parent_tbl
                    on fk.parent_object_id = parent_tbl.object_id
                inner join prpcref_mjp.sys.schemas b
                    on fk.schema_id = b.schema_id')

edited May 18, 2016 at 16:41

answered May 18, 2016 at 4:55

Sufyan Jabr

8195 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

MattP Over a year ago

That just runs the select statement

Sufyan Jabr Over a year ago

@MattP answer updated, there might be some mistakes in the SQL (as i don't have SQL right now) but hopefully you got the idea.

MattP Over a year ago

this is as far as I could get. I can't seem to add a bracket onto the end of the 'exec' statement:

declare @SQLStatement as nvarchar(4000) 	SET @SQLStatement = 'select ''exec(''''alter table prpcref_mjp'' + ''.'' + b.name + ''.'' + parent_tbl.name +  '' NOCHECK CONSTRAINT ''+  fk.name    	from prpcref_mjp.sys.foreign_keys fk    	inner join prpcref_mjp.sys.all_objects parent_tbl     	on fk.parent_object_id = parent_tbl.object_id    	inner join prpcref_mjp.sys.schemas b     	on fk.schema_id = b.schema_id + '')''' 	exec(@SQLStatement)

Sufyan Jabr Over a year ago

you need to think about it in a different way, what am trying to do is a normal select statement with exec, the result will call exec for every result row.

MattP Over a year ago

thanks, makes sense. I've included 'exec' in the select however this only returns a result set and doesn't execute the alter command

SET @SQLStatement = 'select ''exec(''''alter table prpcref_mjp'' + ''.'' + b.name + ''.'' + parent_tbl.name +  '' NOCHECK CONSTRAINT ''+  fk.name  + '''''')''    	from prpcref_mjp.sys.foreign_keys fk      	inner join prpcref_mjp.sys.all_objects parent_tbl        	on fk.parent_object_id = parent_tbl.object_id      	inner join prpcref_mjp.sys.schemas b        	on fk.schema_id = b.schema_id' 	exec(@SQLStatement)

|

MattP · Accepted Answer · 2016-05-19 03:42:12Z

0

    select @NSQL = 'set @SQLStatement_out = ''''
select @SQLStatement_out += ''ALTER TABLE ' + @dbname + '.'' + b.name + ''.'' + parent_tbl.name + '' NOCHECK CONSTRAINT '' + fk.name + '';'' + char(13) + char(10)
  from ' + @dbname + '.' + 'sys.foreign_keys fk
 inner join ' + @dbname + '.' +'sys.all_objects parent_tbl
    on fk.parent_object_id = parent_tbl.object_id
 inner join ' + @dbname + '.' + 'sys.schemas b
    on fk.schema_id = b.schema_id
    exec(@SQLStatement_out) '

SET @ParmDefinition = N'@SQLStatement_out varchar(max) OUTPUT';

exec sp_executesql @NSQL, @ParmDefinition, @SQLStatement_out=@SQLStatement OUTPUT;

exec(@SQLStatement)

answered May 19, 2016 at 3:42

MattP

131 gold badge1 silver badge6 bronze badges

Comments

user212514 · Accepted Answer · 2016-05-19 03:44:45Z

0

Here's another way to approach creating the string that contains all of the DDL statements:

declare @SQLStatement nvarchar(4000) = ''
select @SQLStatement += 'ALTER TABLE prpcref_mjp.' + b.name + '.' + parent_tbl.name + ' DROP CONSTRAINT ' + fk.name + ';' + char(13) + char(10)
  from prpcref_mjp.sys.foreign_keys fk
 inner join prpcref_mjp.sys.all_objects parent_tbl
    on fk.parent_object_id = parent_tbl.object_id
 inner join prpcref_mjp.sys.schemas b
    on fk.schema_id = b.schema_id
print @SQLStatement
exec(@SQLStatement)

You can add a where clause to limit the tables that you're going to modify. Comment out the exec() at the end if you want to see the code before actually running it.

Note that I added + char(13) + char(10) to make the output of the print statement easier to read.

edited May 19, 2016 at 3:44

answered May 18, 2016 at 6:09

user212514

3,1381 gold badge17 silver badges11 bronze badges

1 Comment

MattP Over a year ago

Hey thanks @user212514 that worked a charm. Only issue was that I need to use fully qualified object names in the selects as I'm running it across multiple databases ie. prpcref_mjp.sys.all_objects. I'll provide an answer that reflects this

Collectives™ on Stack Overflow

build and execute statement dynamically in SQL server

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related