所以写了下边的存储过程,拼成的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)