以下是在所有表的数据中搜索关键字，返回表名及字段名的SQL语句：
```
SELECT DISTINCT
c.TABLE_NAME,
c.COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME
WHERE
c.DATA_TYPE IN ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext', 'date', 'datetime', 'datetime2', 'time', 'smalldatetime')
AND t.TABLE_TYPE = 'BASE TABLE'
AND EXISTS (
SELECT
*
FROM
sys.columns sc
INNER JOIN
sys.objects so ON sc.object_id = so.object_id
WHERE
sc.name = c.COLUMN_NAME
AND so.name = c.TABLE_NAME
AND so.type = 'U'
AND (
sc.system_type_id IN (167, 175, 231, 239) -- char, varchar, text, nchar, nvarchar, ntext
OR sc.system_type_id IN (40, 41, 42, 43, 58, 61, 62, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 165, 167, 173, 175, 189, 231, 239) -- date, datetime, datetime2, time, smalldatetime
)
)
AND EXISTS (
SELECT
*
FROM
sys.objects so
INNER JOIN
sys.columns sc ON so.object_id = sc.object_id
INNER JOIN
sys.types st ON sc.system_type_id = st.system_type_id AND sc.user_type_id = st.user_type_id
INNER JOIN
sys.schemas ss ON so.schema_id = ss.schema_id
WHERE
so.type = 'U'
AND so.name = c.TABLE_NAME
AND sc.name = c.COLUMN_NAME
AND (
OBJECT_DEFINITION(so.object_id) LIKE '%'+@keyword+'%'
OR ss.name+'.'+so.name LIKE '%'+@keyword+'%'
OR st.name LIKE '%'+@keyword+'%'
)
)
ORDER BY
c.TABLE_NAME,
c.COLUMN_NAME;
```
需要将其中的 @keyword 替换为需要搜索的关键字。
最低0.47元/天 解锁文章
1021
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
