22,209
社区成员
发帖
与我相关
我的任务
分享
EXEC sp_MSforeachdb 'if "?" not in (''master'',''tempdb'',''model'',''msdb'',''distribution'')
BEGIN
DECLARE @search NVARCHAR(200),@sql NVARCHAR(MAX)
SET @search=''李四'' --自己修改
DECLARE @t TABLE (
rid INT IDENTITY(1,1) PRIMARY KEY,
tableName NVARCHAR(200),
columnName NVARCHAR(200),
printInfo AS tablename+''.''+columnName+'' ''
)
INSERT INTO @t(tableName,columnName)
SELECT t.name,c.name FROM sys.tables AS t
INNER JOIN sys.[columns] AS c ON t.[object_id]=c.[object_id]
INNER JOIN sys.types AS tp ON c.system_type_id=tp.system_type_id AND tp.name IN (''text'',''ntext'',''char'',''nchar'',''varchar'',''nvarchar'')
DECLARE @i INT,@imax INT
SELECT @i=1,@imax=MAX(rid) FROM @t
WHILE @i<=@imax
BEGIN
SELECT @sql=''if exists(select 1 from [''+tableName+''] where convert(nvarchar(max),[''+columnName+'']) like ''''%''+@search+''%'''') select ''''''+DB_NAME()+''.''+printInfo+'''''''' FROM @t WHERE rid=@i;
EXEC(@sql);
SET @i=@i+1;
END
END
'
--1. 创建全局临时表
IF OBJECT_ID('tempdb..##tmp') IS NOT NULL DROP TABLE ##tmp
CREATE TABLE ##tmp(dbName NVARCHAR(100), tableName NVARCHAR(200),pkName NVARCHAR(100),pkValue NVARCHAR(100))
--2. 构建 db 列表的表变量
DECLARE @db TABLE (
[dbId] INT IDENTITY(1,1) PRIMARY KEY,
[dbName] NVARCHAR(MAX)
)
INSERT INTO @db(dbName)
SELECT d.name FROM sys.databases AS d WHERE d.name NOT IN ('master','tempdb','model','msdb')
DECLARE @i INT,@imax INT,@sql NVARCHAR(MAX),@search NVARCHAR(100)
SET @search='李四'
SELECT @i=MIN([dbId]),@imax=MAX([dbId]) FROM @db;
--3. 遍历所有db , 将数据插入到全局临时表
WHILE @i<=@imax
BEGIN
SELECT @sql='use '+dbName +' ' FROM @db WHERE [dbId]=@i;
SET @sql=@sql+'
DECLARE @sql NVARCHAR(MAX)
SET @sql=(
SELECT ''union all select db_name() as dbName,''''''+t.name+'''''' as tableName, ''''''
+ (SELECT c.name FROM sys.[columns] AS c WHERE c.[object_id]=t.[object_id] AND c.column_id=1) +'''''' as pkName, ''
+''[''+ (SELECT c.name FROM sys.[columns] AS c WHERE c.[object_id]=t.[object_id] AND c.column_id=1) +''] as pkValue from ''+t.name
+ '' with(nolock) where ''
+ STUFF((SELECT ''or cast([''+c2.name+''] as nvarchar(max)) = N'''''+@search+''''' '' FROM sys.[columns] AS c2 WHERE c2.[object_id]=t.[object_id]
AND EXISTS(
SELECT 1 FROM sys.types AS p WHERE p.name IN (''text'',''ntext'',''char'',''nchar'',''varchar'',''nvarchar'')
AND p.system_type_id=c2.system_type_id
)
FOR XML PATH('''')),1,2,'''')+'';''
FROM sys.tables AS t FOR XML PATH(''''))
SET @sql=REPLACE(@sql,'';'',''
'')
SET @sql=STUFF(@sql,1,9,'''')
print @sql
insert into ##tmp(dbName,tableName,pkName,pkValue)
EXEC(@sql)
'
EXEC(@sql)
SET @i=@i+1;
END
--4. 查询最终的全局临时表
SELECT * FROM ##tmp;
--1. 创建全局临时表
IF OBJECT_ID('tempdb..##tmp') IS NOT NULL DROP TABLE ##tmp
CREATE TABLE ##tmp(dbName NVARCHAR(100), tableName NVARCHAR(200),pkName NVARCHAR(100),pkValue NVARCHAR(100))
--2. 构建 db 列表的表变量
DECLARE @db TABLE (
[dbId] INT IDENTITY(1,1) PRIMARY KEY,
[dbName] NVARCHAR(MAX)
)
INSERT INTO @db(dbName)
SELECT d.name FROM sys.databases AS d WHERE d.name NOT IN ('master','tempdb','model','msdb')
DECLARE @i INT,@imax INT,@sql NVARCHAR(MAX)
SELECT @i=MIN([dbId]),@imax=MAX([dbId]) FROM @db;
--3. 遍历所有db , 将数据插入到全局临时表
WHILE @i<=@imax
BEGIN
SELECT @sql='use '+dbName +' ' FROM @db WHERE [dbId]=@i;
SET @sql=@sql+'
DECLARE @sql NVARCHAR(MAX)
SET @sql=(
SELECT ''union all select db_name() as dbName,''''''+t.name+'''''' as tableName, ''''''
+ (SELECT c.name FROM sys.[columns] AS c WHERE c.[object_id]=t.[object_id] AND c.column_id=1) +'''''' as pkName, ''
+''[''+ (SELECT c.name FROM sys.[columns] AS c WHERE c.[object_id]=t.[object_id] AND c.column_id=1) +''] as pkValue from ''+t.name
+ '' with(nolock) where ''
+ STUFF((SELECT ''or cast([''+c2.name+''] as nvarchar(max)) = N''''李四'''' '' FROM sys.[columns] AS c2 WHERE c2.[object_id]=t.[object_id] FOR XML PATH('''')),1,2,'''')+'';''
FROM sys.tables AS t FOR XML PATH(''''))
SET @sql=REPLACE(@sql,'';'',''
'')
SET @sql=STUFF(@sql,1,9,'''')
print @sql
insert into ##tmp(dbName,tableName,pkName,pkValue)
EXEC(@sql)
'
EXEC(@sql)
SET @i=@i+1;
END
--4. 查询最终的全局临时表
SELECT * FROM ##tmp;
你这个是所有库、所有表、所有列去扫描, 没什么捷径可以走。
DECLARE @cloumns VARCHAR(40);
DECLARE @tablename VARCHAR(40);
DECLARE @str VARCHAR(40);
DECLARE @counts INT;
DECLARE @sql NVARCHAR(2000);
DECLARE MyCursor CURSOR FOR
SELECT
a.name AS Columns,
b.name AS TableName
FROM
syscolumns a,
sysobjects b,
systypes c
WHERE
a.id = b.id
AND b.type = 'U'
AND a.xtype = c.xtype
AND c.name LIKE '%char%';
SET @str = '值值值值'; --这里写知道的数值
OPEN MyCursor;
FETCH NEXT FROM MyCursor
INTO
@cloumns,
@tablename;
WHILE (@@Fetch_Status = 0)
BEGIN
SET @sql = 'select @tmp_counts=count(*) from ' + @tablename + ' where ' + @cloumns + ' = ''' + @str + '''';
EXECUTE sp_executesql
@sql,
N'@tmp_counts int out',
@counts OUT;
IF @counts > 0
BEGIN
PRINT '表名为:' + @tablename + ',字段名为' + @cloumns;
END;
FETCH NEXT FROM MyCursor
INTO
@cloumns,
@tablename;
END;
CLOSE MyCursor;
DEALLOCATE MyCursor;
DECLARE @cloumns VARCHAR(40);
DECLARE @tablename VARCHAR(40);
DECLARE @str VARCHAR(40);
DECLARE @counts INT;
DECLARE @sql NVARCHAR(2000);
DECLARE MyCursor CURSOR FOR
SELECT
a.name AS Columns,
b.name AS TableName
FROM
syscolumns a,
sysobjects b,
systypes c
WHERE
a.id = b.id
AND b.type = 'U'
AND a.xtype = c.xtype
AND c.name LIKE '%char%';
SET @str = '值值值值'; --这里写知道的数值
OPEN MyCursor;
FETCH NEXT FROM MyCursor
INTO
@cloumns,
@tablename;
WHILE (@@Fetch_Status = 0)
BEGIN
SET @sql = 'select @counts=count(*) from ' +QUOTENAME(@tablename) + ' where ' + QUOTENAME(@cloumns) + ' = ''' + QUOTENAME(@str) + '''';
EXECUTE sp_executesql
@sql,
N'@counts int out',
@counts OUT;
IF @counts > 0
BEGIN
PRINT '表名为:' + RTRIM(@tablename) + ',字段名为' + RTRIM(@cloumns);
END;
FETCH NEXT FROM MyCursor
INTO
@cloumns,
@tablename;
END;
CLOSE MyCursor;
DEALLOCATE MyCursor;