USE [DB_qqq]
GO
/****** Object: StoredProcedure [dbo].[CheckDeleteFieldValueWithExclude] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*@ExcludeXml='<TABLES><TABLE>PARTLABLE</TABLE></TABLES>'*/
ALTER PROC [dbo].[CheckDeleteFieldValueWithExclude] @TableName VARCHAR(50), @FieldName VARCHAR(50),@FieldValue VARCHAR(500),@ExcludeXml VARCHAR(500)
AS
BEGIN
DECLARE @SQL NVARCHAR(2000),@Used INT,@XML XML
DECLARE @Name NVARCHAR(100)
SET @Used=0
SET @XML=@ExcludeXml
DECLARE Cur Cursor FOR
SELECT ob.name FROM SYS.columns cl ,sys.objects ob WHERE cl.name=@FieldName AND cl.object_id=ob.object_id
AND ob.type='U' AND ob.name<>@TableName
Open Cur
Fetch NEXT From Cur Into @Name
While @@fetch_status=0
BEGIN
--检查表是否排除
DECLARE @COUNTNUM INT
SET @COUNTNUM=0
SELECT @COUNTNUM=COUNT(*) FROM
(SELECT N.value( '(text())[1]','NVARCHAR(MAX)' ) TABLENAME
FROM @XML.nodes('/TABLES/TABLE') V(N) ) as temp
where TABLENAME=@Name
IF @COUNTNUM>0
BEGIN
Fetch Next From Cur Into @Name
CONTINUE
END
SET @SQL=' Select @Used = Count('+@FieldName +') from '+@Name +' Where '+@FieldName +'='+ ''''+@FieldValue+''''
exec sp_executesql @sql,N'@Used int out',@Used out
if @Used>0
break
Fetch Next From Cur Into @Name
END
IF @Used=0
SELECT '' AS USE_TABLE
ELSE
SELECT @Name AS USE_TABLE
CLOSE Cur
DEALLOCATE Cur
END
