很牛逼的脚本, 如下:
- 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
执行如下:
exec [SP_FindValueInDB] 'e0896be2-cbd3-418a-84e2-040ac15c02cd'
执行结果:
参考博客: https://www.cnblogs.com/nov5026/p/8630150.html
@陈卧龙的博客
来源: http://www.bubuko.com/infodetail-2968601.html