230.搜索指定数据在那个对象中存在

梦里梦外; 2023-12-28 06:27 16阅读 0赞

CREATE PROC sp_ValueSearch
    @value sql_variant,  --要搜索的数据
    @precision bit=1     --1=仅根据sql_variant中的数据类型查找对应类型的数据列.<>1,查询兼容的所有列,字符数据使用like匹配
    AS
    SET NOCOUNT ON
    IF @value IS NULL RETURN
    
    --数据类型处理
    SELECT xtype INTO #t FROM systypes
    WHERE name=SQL_VARIANT_PROPERTY(@value,N'BaseType')
    
    --扩展数据类型及查询处理语句
    DECLARE @sql nvarchar(4000),@sql1 nvarchar(4000)
    IF @precision=1
    	SET @sql=CASE SQL_VARIANT_PROPERTY(@value,N'BaseType')
    		WHEN N'text' THEN N' LIKE N''%''+CAST(@value as varchar(8000))+''%'''
    		WHEN N'ntext' THEN N' LIKE ''%''+CAST(@value as nvarchar(4000))+''%'''
    		ELSE N'=@value' END
    ELSE
    BEGIN
    	SET @sql=CAST(SQL_VARIANT_PROPERTY(@value,N'BaseType') as sysname)
    	IF @sql LIKE N'%char' or @sql LIKE N'%text'
    	BEGIN
    		INSERT #t SELECT xtype FROM systypes
    		WHERE name LIKE N'%char' or name LIKE N'%text'
    		SELECT @sql=N' LIKE N''%''+CAST(@value as '
    			+CASE 
    				WHEN LEFT(@sql,1)=N'n' THEN ' nvarchar(4000)'
    				ELSE 'varchar(8000)' END
    			+N')+N''%'''
    	END
    	ELSE IF @sql LIKE N'%datetime'
    	BEGIN
    		INSERT #t SELECT xtype FROM systypes
    		WHERE name LIKE N'%datetime'
    		SET @sql=N'=@value'	
    	END
    	ELSE IF @sql LIKE N'%int' 
    		OR @sql LIKE N'%money' 
    		OR @sql IN(N'real',N'float',N'decimal',N'numeric')
    	BEGIN
    		INSERT #t SELECT xtype FROM systypes
    		WHERE name LIKE N'%int' 
    			OR name LIKE N'%money' 
    			OR name IN(N'real',N'float',N'decimal')
    		SET @sql=N'=@value'	
    	END
    	ELSE
    		SET @sql=N'=@value'
    END
    --保存结果的临时表
    CREATE TABLE #(TableName sysname,FieldName sysname,Type sysname,SQL nvarchar(4000))
    
    DECLARE tb CURSOR LOCAL
    FOR
    SELECT N'SELECT * FROM '
    	+QUOTENAME(USER_NAME(o.uid))
    	+N'.'+QUOTENAME(o.name)
    	+N' WHERE '+QUOTENAME(c.name)
    	+@sql,
    	N'INSERT # VALUES(N'+QUOTENAME(o.name,N'''')
    	+N',N'+QUOTENAME(c.name,N'''')
    	+N',N'+QUOTENAME(QUOTENAME(t.name)+CASE 
    		WHEN t.name IN (N'decimal',N'numeric')
    		THEN N'('+CAST(c.prec as varchar)+N','+CAST(c.scale as varchar)+N')'
    		WHEN t.name=N'float'
    			OR t.name like N'%char'
    			OR t.name like N'%binary'
    		THEN N'('+CAST(c.prec as varchar)+N')'
    		ELSE N'' END,N'''')
    	+N',@sql)'
    FROM sysobjects o,syscolumns c,systypes t,#t tt
    WHERE o.id=c.id
    	AND c.xusertype=t.xusertype
    	AND t.xtype=tt.xtype
    	AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
    
    OPEN tb
    FETCH tb INTO @sql,@sql1
    WHILE @@FETCH_STATUS=0
    BEGIN
    	SET @sql1=N'IF EXISTS('+@sql+N') '+@sql1
    	EXEC sp_executesql @sql1,N'@value sql_variant,@sql nvarchar(4000)',@value,@sql
    	FETCH tb INTO @sql,@sql1
    END
    CLOSE tb
    DEALLOCATE tb
    SELECT * FROM #