查询数据库中的相同值得所有表跟字段【存储过程】

IF OBJECT_ID(N‘usp_QueryAllTableFieldContent‘) IS NOT NULL
BEGIN
	DROP PROC usp_QueryAllTableFieldContent
END
GO
CREATE PROCEDURE usp_QueryAllTableFieldContent
    (        
      @queryFieldType VARCHAR(max) ,  --要查询的类型      
	  @queryContent VARCHAR(max),  --要查询的内容 
	  @queryFieldName VARCHAR(max)=‘‘ --要查询的字段名称      
    )
AS 
BEGIN
	--创建从数据库查询来的表名，字段名的临时表
	CREATE TABLE #TmpQueryTbl
	(
		id INT IDENTITY(1, 1) ,
		tableName VARCHAR(200),
		fieldName VARCHAR(200)
	)     
	DECLARE @strSqlSys VARCHAR(max)	
	SET @strSqlSys=
	‘INSERT INTO #TmpQueryTbl(tableName,fieldName)‘+
	 ‘ SELECT sysobjects.name AS TableName, syscolumns.name AS ColumnsName FROM syscolumns‘+
	 ‘ INNER JOIN sysTypes ON sysTypes.xtype=syscolumns.xtype INNER JOIN sysobjects ON sysobjects.id = syscolumns.id‘+     
	‘ WHERE sysTypes.name=‘‘‘+@queryFieldType+‘‘‘ AND sysobjects.xtype=‘+‘‘‘U‘‘‘+‘ AND syscolumns.name IS NOT NULL‘
	IF @queryFieldName IS NOT NULL AND @queryFieldName<>‘‘
	BEGIN
		SET @strSqlSys=@strSqlSys+‘ AND syscolumns.name=‘‘‘+@queryFieldName+‘‘‘‘
	END
	SET @strSqlSys=@strSqlSys+‘ ORDER BY syscolumns.name‘
	
	EXEC(@strSqlSys)		
	
	
	--开始根据临时表中的表名和字段名，查询数据	 					 
	DECLARE @intCount INT                  
	SET @intCount = @@rowcount 
	--创建结果表
	CREATE TABLE #TmpResultTbl
	(
		id INT IDENTITY(1, 1) ,
		tableName VARCHAR(200),
		fieldName VARCHAR(200),
		strContent VARCHAR(max)
	)     
	DECLARE @strSql VARCHAR(max)
	DECLARE @tableName VARCHAR(200)
	DECLARE @fieldName VARCHAR(200)
	DECLARE @intI INT = 1   
	WHILE @intI <= @intCount                     
        BEGIN
			SELECT @tableName=tableName,@fieldName=fieldName FROM #TmpQueryTbl WHERE id= @intI
			SET @strSql=‘INSERT INTO #TmpResultTbl (tableName,fieldName,strContent)‘ +
			  ‘ SELECT ‘+‘‘‘‘+@tableName+‘‘‘‘+‘,‘+‘‘‘‘+@fieldName+‘‘‘‘+‘,[‘+@fieldName+‘] FROM [‘ +@tableName+‘] WHERE [‘
			  +@fieldName+‘]=‘+‘‘‘‘+@queryContent+‘‘‘‘
			  EXEC(@strSql)   
			 SET @intI = @intI + 1     
        end 
   
	SELECT *FROM #TmpResultTbl  
    DROP TABLE #TmpQueryTbl 
    DROP TABLE #TmpResultTbl
 
END
 
 
  
      
       
                    
                     

查询数据库中的相同值得所有表跟字段【存储过程】

标签：sql   content   objects   临时表   var   declare   inner   字段   arch