查询整个数据库中某个特定值所在的表和字段的方法
方法1：
CREATE PROCEDURE [dbo].[SP_FindValueInDB]
(
@value VARCHAR(1024)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @sql VARCHAR(1024)
DECLARE @table VARCHAR(64)
DECLARE @column VARCHAR(64)
CREATE TABLE #t (
tablename VARCHAR(64),
columnname VARCHAR(64)
)
DECLARE TABLES CURSOR
FOR
SELECT o.name, c.name
FROM syscolumns c
INNER JOIN sysobjects o ON c.id = o.id
WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239)
ORDER BY o.name, c.name
OPEN TABLES
FETCH NEXT FROM TABLES
INTO @table, @column
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] '
SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) LIKE ''%' + @value + '%'') '
SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''
SET @sql = @sql + @column + ''')'
EXEC(@sql)
FETCH NEXT FROM TABLES
INTO @table, @column
END
CLOSE TABLES
DEALLOCATE TABLES
SELECT *
FROM #t
DROP TABLE #t
End
方法2：
01 |
CREATE PROC sp_ValueSearch |
02 |
@value sql_variant, --要搜索的数据 |
03 |
@precision bit=1 --1=仅根据sql_variant中的数据类型查找对应类型的数据列.<>1,查询兼容的所有列,字符数据使用like匹配 |
04 |
AS |
05 |
SET NOCOUNT ON |
06 |
IF @value IS NULL RETURN |
07 |
|
08 |
--数据类型处理 |
09 |
SELECT xtype INTO #t FROM systypes |
10 |
WHERE name=SQL_VARIANT_PROPERTY(@value,N'BaseType') |
11 |
|
12 |
--扩展数据类型及查询处理语句 |
13 |
DECLARE @sql nvarchar(4000),@sql1 nvarchar(4000) |
14 |
IF @precision=1 |
15 |
SET @sql=CASE SQL_VARIANT_PROPERTY(@value,N'BaseType') |
16 |
WHEN N'text' THEN N' LIKE N''%''+CAST(@value as varchar(8000))+''%''' |
17 |
WHEN N'ntext' THEN N' LIKE ''%''+CAST(@value as nvarchar(4000))+''%''' |
18 |
ELSE N'=@value' END |
19 |
ELSE |
20 |
BEGIN |
21 |
SET @sql=CAST(SQL_VARIANT_PROPERTY(@value,N'BaseType') as sysname) |
22 |
IF @sql LIKE N'%char' or @sql LIKE N'%text' |
23 |
BEGIN |
24 |
INSERT #t SELECT xtype FROM systypes |
25 |
WHERE name LIKE N'%char' or name LIKE N'%text' |
26 |
SELECT @sql=N' LIKE N''%''+CAST(@value as ' |
27 |
+CASE |
28 |
WHEN LEFT(@sql,1)=N'n' THEN ' nvarchar(4000)' |
29 |
ELSE 'varchar(8000)' END |
30 |
+N')+N''%''' |
31 |
END |
32 |
ELSE IF @sql LIKE N'%datetime' |
33 |
BEGIN |
34 |
INSERT #t SELECT xtype FROM systypes |
35 |
WHERE name LIKE N'%datetime' |
36 |
SET @sql=N'=@value' |
37 |
END |
38 |
ELSE IF @sql LIKE N'%int' |
39 |
OR @sql LIKE N'%money' |
40 |
OR @sql IN(N'real',N'float',N'decimal',N'numeric') |
41 |
BEGIN |
42 |
INSERT #t SELECT xtype FROM systypes |
43 |
WHERE name LIKE N'%int' |
44 |
OR name LIKE N'%money' |
45 |
OR name IN(N'real',N'float',N'decimal') |
46 |
SET @sql=N'=@value' |
47 |
END |
48 |
ELSE |
49 |
SET @sql=N'=@value' |
50 |
END |
51 |
--保存结果的临时表 |
52 |
CREATE TABLE #(TableName sysname,FieldName sysname,Type sysname,SQL nvarchar(4000)) |
53 |
|
54 |
DECLARE tb CURSOR LOCAL |
55 |
FOR |
56 |
SELECT N'SELECT * FROM ' |
57 |
+QUOTENAME(USER_NAME(o.uid)) |
58 |
+N'.'+QUOTENAME(o.name) |
59 |
+N' WHERE '+QUOTENAME(c.name) |
60 |
+@sql, |
61 |
N'INSERT # VALUES(N'+QUOTENAME(o.name,N'''') |
62 |
+N',N'+QUOTENAME(c.name,N'''') |
63 |
+N',N'+QUOTENAME(QUOTENAME(t.name)+CASE |
64 |
WHEN t.name IN (N'decimal',N'numeric') |
65 |
THEN N'('+CAST(c.prec as varchar)+N','+CAST(c.scale as varchar)+N')' |
66 |
WHEN t.name=N'float' |
67 |
OR t.name like N'%char' |
68 |
OR t.name like N'%binary' |
69 |
THEN N'('+CAST(c.prec as varchar)+N')' |
70 |
ELSE N'' END,N'''') |
71 |
+N',@sql)' |
72 |
FROM sysobjects o,syscolumns c,systypes t,#t tt |
73 |
WHERE o.id=c.id |
74 |
AND c.xusertype=t.xusertype |
75 |
AND t.xtype=tt.xtype |
76 |
AND OBJECTPROPERTY(o.id,N'IsUserTable')=1 |
77 |
|
78 |
OPEN tb |
79 |
FETCH tb INTO @sql,@sql1 |
80 |
WHILE @@FETCH_STATUS=0 |
81 |
BEGIN |
82 |
SET @sql1=N'IF EXISTS('+@sql+N') '+@sql1 |
83 |
EXEC sp_executesql @sql1,N'@value sql_variant,@sql nvarchar(4000)',@value,@sql |
84 |
FETCH tb INTO @sql,@sql1 |
85 |
END |
86 |
CLOSE tb |
87 |
DEALLOCATE tb |
88 |
SELECT * FROM # |
