所以写了下边的存储过程,拼成的sql文变量里有exec( )方法,最后又调用了exec( )方法,有点绕。
ALTER procedure [dbo].[DropTablePK](@TableName varchar(1000))
AS
declare @exec_sql varchar(1000)
set @exec_sql='declare @PK_Name varchar(30) declare @exec_sql varchar(300)'
set @exec_sql=@exec_sql+' select @PK_Name=name FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].['
set @exec_sql=@exec_sql+@TableName+']'') '
set @exec_sql=@exec_sql+'set @exec_sql=''ALTER TABLE [dbo].['+@TableName+'] drop CONSTRAINT ['' +@PK_Name+'']'' exec(@exec_sql)'
exec(@exec_sql)
AS
declare @exec_sql varchar(1000)
set @exec_sql='declare @PK_Name varchar(30) declare @exec_sql varchar(300)'
set @exec_sql=@exec_sql+' select @PK_Name=name FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].['
set @exec_sql=@exec_sql+@TableName+']'') '
set @exec_sql=@exec_sql+'set @exec_sql=''ALTER TABLE [dbo].['+@TableName+'] drop CONSTRAINT ['' +@PK_Name+'']'' exec(@exec_sql)'
exec(@exec_sql)