做项目中前些天要手动改数据库中的一些字段的值,但发现这个字段可能会被数据库中其他的很多张表引用,于是就得想办法查询出该条记录的所有引用的表和字段,下面的存储过程可以查询出整个数据库中某个特定值所在的表和字段。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
CREATE
PROCEDURE
[dbo].[SP_FindValueInDB]
(
@value
VARCHAR
(1024)
)
AS
BEGIN
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
|
只需要传入一个想要查找的值,即可查询出这个值所在的表和字段名。
1
|
exec
[SP_FindValueInDB]
'4f02e90d-9a75-4bd9-a98f-3b6e0851c29a'
|
查询出来的结果如下: