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
